Monday, 8 August 2011

Putting a Database into Single User Mode

I was recently discussing with a group of budding DBA’s how you go about performing a restore of database. They were trying the restore for themselves  and a few of them got an error about not having exclusive access:

Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

This was a test setup and obviously we had other sessions connecting to the databases. these connections were  preventing the restore.

If you are restoring a database and you have other sessions connected it can be quite simple to kill those connections in one go  by putting the database into single user mode and using the ‘ROLLBACK IMMEDIATE’ switch. This will rollback any open transactions and allow you to restore the database. I like to wrap this around the restore statement

alter database TestDB set single_user with rollback immediate 



Most of this information including example code is available in Books Online, which is great document for all SQL Server professionals to make use of?

1 comment:

  1. I don't get people thinking their data won't be deleted or erased. Today, there are lots of options on how to save and restore deleted files; online or through a back-up software.

    I think a lot of people don't bother with back-up plans because they thought it is for the techy folks. WRONG. As a matter of fact. backing-up your data is very easy. All you have to do is follow the instructions, and you're all set.


Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...