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:

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?

Continue reading on SQLStudies.com.

Continue reading on SQLStudies.com.