Earlier this week I came across an interesting situation where a tail log backup of some databases were taken before a migration – meaning a final transaction log backup was taken and the database was set into RESTORING state. The interesting part came where we recovered the same database on the same server (not restored from backup). We found that service broker had been disabled as part of recovering the database.
I’ve previously blogged on this situation happening when restoring a database or attaching a database loses some database settings. In that situation, the database owner changes to the user performing the attach or restore, read-only changes back to multi user, as well as trustworthy, DB chaining and broker get disabled. However this situation is a bit different since we’re not detaching a database first, or restoring a new database. This is the same database that hasn’t at any point been removed from the SQL Server instance.
In this situation when we recover a database from RESTORING state, the database owner remains the same and both trustworthy and DB chaining remain enabled if they were before. You can’t take a tail log backup while a database is in read-only mode. So that leaves us with the only database setting that is being changed – broker.
Let me demonstrate this. I’ll first enable the database settings, Trustworthy, Cross Database Ownership Chaining and Service Broker on my database called RecoverTest. The database is in ONLINE state and the database owner is Jacinta. You can see below:
I will now take a tail log backup. Note the NORECOVERY argument in the backup command:
I will check the database settings again, to ensure it’s now in RESTORING state. Note that all settings other than the state, remain the same:
Now lets recover the database:
Cool the database is recovered. Better check the database settings again:
Woohoo, the database is recovered. The database owner has remained the same, as has the trustworthy and db chaining settings. But look at the is_broker_enabled column. Service Broker has been disabled as part of the database recovery! Not cool. That’s the last thing you need when you’re trying to recover a database when things possibly already haven’t gone to plan.
Luckily, it’s an easy fix – now we know the problem. Just enable it again.
ALTER DATABASE [RecoverTest] SET ENABLE_BROKER WITH NO_WAIT;
Unfortunately, to enable Service Broker, you need exclusive access in the database. So you can either kill all your sessions, or run the same command as above, but instead with rollback immediate:
ALTER DATABASE [RecoverTest] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
After running that, lets check the database settings again:
So with all this said and done, my advice to you all is: in your flight plans and your rollback plans, if you are recovering a database after a tail log backup has been performed, ensure to have another step to enable service broker if you need it enabled.
Chad Franklin 