When restoring a database, one of the things you need to do is ensure that you have exclusive access to the database. If any other users are in the database the restore will fail.
When trying to do a restore, if any other user is in the database you will see these types of error messages:
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Getting Exclusive Access
To get exclusive access, all other connections need to be dropped or the database that they are in needs to be changed so they are not using the database you are trying to restore. You can use sp_who2 or SSMS to see what connections are using the database you are trying to restore.
One option to get exclusive access is to use the KILL command to kill each connection that is using the database., but be aware of what connections you are killing and the rollback issues that may need to occur. See this tip for more information on how to do this.
Using ALTER DATABASE
Another option is to put the database in single user mode and then do the restore. This also does a rollback depending on the option you use, but will do all connections at once. See this tip for more information on how to do this.
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' GO
To set a database to single-user mode
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- Copy and paste the following example into the query window and click Execute. This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.
USE master; GO ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE AdventureWorks2012 SET READ_ONLY; GO ALTER DATABASE AdventureWorks2012 SET MULTI_USER; GO