Home > SQL Server > Restore SQL Server Database – disconnect all users

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.

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: