Restore Database failed: Exclusive access could not be obtained because database is in use

This is quite common error message we see, when we restore database. This occurs if any other users connected to the database you tried to restore.

We have different of methods to resolve this based on our requirement.

1)      Alter Database to Single User Mode

Changing database to single user mode roll backs all connections at once.

ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE DATABASE_NAME FROM DISK = 'C:\DATABASE_NAME.BAK'

GO

2)      Kill all active connections on database you need to restore.

Use SP_WHO2 to find connections active on that database. Use KILL command to kill all the connections and then restore the database.

KILL (SPID)

3)      Make database OFFLINE (this will kill all the connections on that database), make database back ONLINE. Now perform restore database task.

ALTER DATABASE DATABASE_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE DATABASE_NAME SET ONLINE

GO