Doing a database restore may not be the most common task a database professional will ever do, but it’s a lot more frequent than you might think. Operational restores are not exactly uncommon for example. Now, when you are doing a recovery like this, what is common is that there are people in the database. (Honestly at least half the time I’m one of them.) And of course when we try to do the restore we get the following error:

Msg 3101, Level 16, State 1, Line 2

Exclusive access could not be obtained because the database is in use.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Ok, so the first thing to do is find out who is currently using the database. It’s easy enough. There are sp_who, sp_whoisactive, and sys.dm_tran_locks just to name a few. But wait, I hear some of you call, Can’t I just use ROLLBACK IMMEDIATE?

Quick break to explain what ROLLBACK IMMEDIATE is for those of you who don’t know. For certain ALTER DATABASE commands you can add the ROLLBACK option. The ROLLBACK option tells SQL to kill all connections to the database and roll back any transactions currently open. The IMMEDIATE part is how long to wait before doing it. FYI, yes, you can specify an amount of time to wait rather than immediately. Not all ALTER DATABASE commands can use ROLLBACK IMMEDIATE (not sure why) so I usually use:

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

You do have to grab it pretty quickly to prevent someone else from being the single user so I frequently script the restore out first, put it right under the ALTER DATABASE and run them both at once. FYI, single user mode also means you can’t use Object Explorer to do your restore (it takes more than one connection as I understand it) but that’s ok. If you are feeling lucky you can always switch it right back to MULTI_USER so you can use OE.

Ok, so now we know we can dump everyone out of the database and perform our restore right after that. Yay us!

But, let’s take a minute and think about this a bit closer. Should we? Should we dump everyone out of the database? Are you sure?

• Well, it’s dev and we’ve talked to the development team. No one is doing anything right now.
• It’s prod so we’ve been extra careful. We arranged an outage with the business and confirmed that now is a good time.

Well, in theory, we’ve been told no one is using the database. However, that’s theory, this is reality, and in fact, someone is in the database. But what are they doing? Is it just a leftover connection? Someone left a query window open and forgot about it? The application creates a connection and holds on to it 24×7 even if it’s not doing anything?

Or: Is there an important nightly process running right now? Is there a user who wasn’t told what’s going on or a developer who is running a big import?

And your first thought is Who cares? I’m overwriting the database. Whatever they are doing is going to be gone in a few minutes anyway.

Well, maybe. What if they are generating output? What if there is important data in there and someone is trying to export it at the last minute? Or far more likely what if that nightly process has been importing data for the last 6 hours, or the developer for that matter. Heck, the developer might have finished their import but forgot to commit the transaction. When you kill that connection it’s going to have to roll back. And that might only take a few minutes, or, as I’ve seen in the past, you might be sitting there for hours waiting.

The thing is, you just don’t know. Check. It takes <5 minutes, particularly if you already have scripts written to do the work. Check for open transactions, check for active sessions, etc. Then, if anything is actually open or running, contact the person. Or if that's not possible contact their manager, or as a last case contact yours. And yes, if you are working at 3am you had better have a manager's phone number who is aware that you are working and may need to call them so they can make a decision. That said, I’d make sure there is actually something going on before waking up your manager

Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: best practices, kill, microsoft sql server, Restore