SQLBkup32 - Restore: Exclusive access could not be obtained

Brian Donahue

• Date: 5 Sep 2005
• Versions Affected: SQL Backup 3.x

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:

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:

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:

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:

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 this article explains some of the more unusual reasons why a SQL Backup restore job would fail because the server doesn't have exclusive access.