Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 

RESTORE DATABASE [TestDB]
FROM DISK = N'C:\Data\SQLBackup\TestDB.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5

GO




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?


Comments

Posted by Jason Brimhall on 8 August 2011

Thanks for reminding us of basics.  ;)

Leave a Comment

Please register or log in to leave a comment.