Restoring the database: exclusive access could not be obtained

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.
quasar_eeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin HillSr. SQL Server DBACommented:
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...)
csachdevaCommented:
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
LordSMCommented:
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)));
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.