Check that database before you restore it…

I'm on my way to the city of Seattle this morning on the train and I ran across an issue with a database restore. I've been working with Service Broker and then put it out of my mind. I then backed up said database and restored it the same instance as a test. Then things started going awry...

Then this little voice in my head (not the one that tells me that people are out to get me) told me to check something in Service Broker. The deal is that Service Broker uses a GUID to track conversations. That's when it clicked - the GUID is at the Database level, not the Instance or Schema. That means when I restored the database I had two DBs with the same Service Broker GUID. Doh!

So how to check this? Well, I've added the following check to my database backup scripts - here's one for AdventureWorks:

   1: SELECT is_broker_enabled 
   2: FROM sys.databases
   3: WHERE name = 'AdventureWorks';
   4: GO

If I get a true condition, I make sure that I send a notification to make sure to run a script like this one on the restored database, if it is going to the same system:

   1: ALTER DATABASE AdventureWorks 
   3: GO 

Of course, it's not a bad idea to run that script anyway. Then I find this: It seems that SQL Server 2008 knows about this problem and even has a tool to detect it! How cool.

Skip to main content