Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Restoring a database when an application or user is still connected

Posted by rahmanagoro on November 2, 2010


Restoring a database whilst application connections persists to the database. I have a .NET application which continually needs to query the database to fetch data therefore opening a connection to the database, the problem is that I need to refresh the data on testing with recent data from production. If one tries to restore the database, you get the error.

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.

In order to get around this problem, what I tend to do is set the database offline, do the restore and set it back online. This is to prevent the application from continuously connection to the database thereby failing the restore. The scripts are shown on this post.

Advertisements

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: