Sunday, October 28, 2012

[SOLVED] Sql restore error: database is in use.

Sometimes you are trying to restore a sql server database and you'll get the error:
"Exclusive access could not be obtained because the database is in use."

Exclusive access could not be obtained because the database is in use.

This simple one-liner will kick everyone off SQL Server (including any inadvertant connections from yourself e.g. edit table connections).

USE MASTER;
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This technique works fine on Microsoft SQL Server 2005 / 2008 and Express Editions (probably works on more versions). It works very quickly and allows you to restore you database straight away afterwards.

This excellent tip was found on dba stackexchange. Please let me know if you found this tip helpful.

1 comment:

Unknown said...

One another solution to fix this issue is to set database offline before running the restore option.

Here is the command:
alter database MyDatabase set offline with rollback immediate