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
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?