Restore SQL Server Database – disconnect all users

Hi,

Often when you try to restore a database there will be connections to the DB, which will stop the restore from succeeding.

What I do is use the wizard to configure the restore, and then click the script button, to script the restore to the clipboard, so that I can run it as a TSQL statement.

The trick is you want to disconnect all sessions, so you send the Database into single user mode which will disconnect all sessions. After the restore you then set the database back to normal.

The code would look like

Alter Database YOURDB Set SINGLE_USER with Rollback Immediate
GO

RESTORE DATABASE [YOURDB] FROM  DISK = N'F:\SQLBackup\YOURDB.bak' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10
GO

Alter Database YOURDB Set MULTI_USER 
GO

The important bits are the Alter Database statements either side of the restore database script.