SQL Server Advanced Restore

If a database is “in use” during attempted restore, you will get an error like the following:

Msg 3101, Level 16, State 1, Line 1
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.

To solve the problem, write a restore SQL script and start it with a line like the following. *WARNING* this command will deny access to all users except the currently running script … MAKE SURE YOU KNOW WHAT YOU’RE DOING!! I usually run the script from an elevated “administrator” command-line using the “sqlcmd -i input_file” tool.

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

If restoring a backup onto a different server, SQL native user accounts will need to be re-mapped. Add a command like the following to the *end* of your restore script:

USE [dbname]
GO
ALTER USER [username] WITH LOGIN = [username], DEFAULT_SCHEMA=[dbo]
GO

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Uncategorized. Bookmark the permalink.

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