[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restoring the database: exclusive access could not be obtained

Posted on 2006-04-20
4
Medium Priority
?
30,239 Views
Last Modified: 2011-08-18
While restoring the SQL Server database, I got the message:
Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.

Are there other ways of making it work except of restarting db server or dropping the database?
Thanks.
0
Comment
Question by:quasar_ee
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 16504086
Hi quasar_ee,
you  should start SQl server in Single user mode and retry.

otherwise run sp-who2 and find any users are in use
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 140 total points
ID: 16507838
Or, in Enterprise manager look in management>>Current Activity>>Process Info

Sort by database name for the user and application accessing the db.

For those that don't like QA based utilities such as sp_who2 (which is a fine answer as well...)
0
 
LVL 4

Assisted Solution

by:csachdeva
csachdeva earned 160 total points
ID: 16513581
SQL Server does not allow you to restore a database to a server if the database is currently in use by other users. The obvious solution is to perform restores when nobody is using the database either by doing it after business hours or perhaps by adding some SQL to your restore script that will kill any user sessions in that database.

There are, however, two subtle reasons why this can happen when you restore a database with SQL Backup using a custom script.

If you are restoring a database from one SQL Server instance to another SQL Server instance on the same computer, you may get this error: Quote:
SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
Serial number: 010-001-NNNNNN-NNNN
Restoring database KeyOfE from "c:\SQLBackups\KeyOfE(full).sqb"
VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the S
Msg 3101, Level 16, State 2, Server SYMPHONY, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SYMPHONY, Line 1
RESTORE DATABASE is terminating abnormally.
In this case, the backup script was run in Query Analyzer which was connected to an instance of the local server, SYMPHONY\CODA: Quote:
EXEC master..sqlbackup '-SQL "RESTORE DATABASE [KeyOfE] From DISK=''c:\SQLBackup\KeyOfE(full).sqb'' WITH MOVE ''KeyOfE_Data'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL$CODA\data\KeyOfE_Data.MDF'', MOVE ''KeyOfE_Log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL$CODA\data\KeyOfE_Log.LDF''" -E'
The problem that has occurred here is that the restore command has omitted the -I parameter to SQL Backup. Without specifying the instance name that SQL Backup needs to operate on, it will choose the default instance, which is more than likely in use if not by yourself, then by other users. The key is in the error message. Even though you have run the query under the context of SYMPHONY\CODA's master database, the error message indicates that the server returning the error is called SYMPHONY. In that case, you need to tell SQL Backup to restore to the CODA instance: Quote:
master..sqlbackup '-SQL "RESTORE DATABASE [KeyOfE] From DISK=''c:\SQLBackup\KeyOfE(full).sqb'' WITH MOVE ''KeyOfE_Data'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL$CODA\data\KeyOfE_Data.MDF'', MOVE ''KeyOfE_Log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL$CODA\data\KeyOfE_Log.LDF''" -E -I CODA'


The other example of this can occur in your scheduled SQL Agent job for log shipping. When the log shipping job runs on the receiving server, the job may fail and the following message is left in the error log: Quote:
Exclusive access could not be obtained because the database is in use.
This could be caused by the first issue, but it could also be a configuration problem with the job. Every SQL Agent job runs in the context of a databse. If you examine the properies of the job step in Enterprise Manager for restoring the logs to the server, you will see a Database dropdown. If the database name specified there is the same one you are restoring to, you will get an error message when the job runs because SQL Agent is making a connection to it. To fix this, simply change the database name in the job step to master or some other database.

hopefully it simplifies your problem.

Regards,
Chetan Sachdeva
0
 

Expert Comment

by:LordSM
ID: 21464642
Go To Management, Activity Monitor and kill the processes that are accessing the database. These processes are the ones that interupt the restore. This should work.

Also remember to clear your connection pool after you restore your database else you may find  'A transport-level error' . This error can be removed by
System.Data.SqlClient.SqlConnection.ClearPool(new System.Data.SqlClient.SqlConnection(string.Format(YOURCONNECTIONSTRING)));
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question