Exclusive access could not be obtained (database in use?)

jgoemaat

Hello! Great product so far - just having issues with a restore on one of our servers. I just installed the same, new version (3.1.0) of SQL Backup on 3 of our servers. We backup from one and retore to the other two. One of them is not restoring correctly, giving me the following error:

SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
Serial number: 010-005-021891-E64D
Restoring database Tango_Reporting from "H:\Backup\TangoPart1.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 AQUARIUS, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server AQUARIUS, Line 1
RESTORE DATABASE is terminating abnormally.

I verified all 3 of our servers are running SQL Server with SP3 on Windows 2000.

I am running the following SQL from SQL Query Analyzer (with passwords changed of course) against the master database. From everything I have read and done in the psat, when I have performed native SQL Server restores, the ALTER DATABASE command has always killed any existing connections to a database allowing the restore to continue.

ALTER DATABASE Tango_Reporting SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

exec master..sqlbackup '-SQL "RESTORE DATABASE [Tango_Reporting] FROM DISK = ''H:\Backup\TangoPart1.sqb'', DISK = ''H:\Backup\TangoPart2.sqb''  WITH  RECOVERY, MOVE ''Tango_Data'' TO ''G:\SQLData\MSSQL\Data\Tango_Data.MDF'', MOVE ''Tango_Log'' TO ''G:\SQLData\MSSQL\Data\Tango_Log.LDF'', PASSWORD = ''<ENCRYPTEDPASSWORD>password</ENCRYPTEDPASSWORD>''" -U sa -P password
GO

I also get the same error from the SQL Backup GUI AND running from the command-line.

Using both the SQL Backup and SQL Server process viewers, I can't find any processes using the database I'm trying to restore to.

Any ideas? Thanks!

petey

Single user access won't work with SQL Backup restores. The reason is because one connection is used to issue the SQL Backup restore command (yours), and another connection is required to issue a regular T-SQL restore command to work with the virtual device (issued by SQL Backup to the SQL Server engine).

The workaround (though not perfect) would be to run a script prior to the restore to kill all other connections except for yours.

petey

Just to elaborate a little further.

If you set the SINGLE_USER option from within Query Analyzer, it appears that it assumes that you will be accessing that single user database eventually, and disallows any other connections even though you may be connected to another database in QA. This is the connection that is blocking the restore. You can see this by running sp_who while SQL Backup is running, and identify the blocking connection.

The workaround is to disconnect the connection that set the database to single_user mode and start SQL Backup immediately before another user connects or simply set the connection back to multi user and hope SQL Backup connects before any other user.

You mentioned that you are restoring to two servers and only one fails. Was the other set to single user made prior to the restore too?

jgoemaat

Thanks for the quick reply!

I think your idea of to set the database to single_user mode then set the connection back to multi_user and run SQL Backup right away before any other user connects may work.

Originally I was not using SINGLE_USER I was using RESTRICTED_USER meaning "only members of the db_owner, dbcreator, or sysadmin roles can use the database" - and got the same error. I wasn't aware two connections were used for the restore, so what you're saying makes sense.

The database restore that works fine runs on our redundant server, so no-one is using that and I don't run any ALTER DATABASE commands. The database restore that is erroring is our "reporting SQL server" that numerous users query against during the day. Since I'm doing the restore at 5:00AM, I figured no connections were open, but perhaps someone is leaving a connection open.

I'll give your great idea a try and let you know how it goes - thansk again!

petey

I may have worded my replies a little ambiguously. Two connections aren't 'really' needed for a restore. The problem is that the connection that issues the ALTER DATABASE SET SINGLE_USER .. command is automatically set as the only user, even though it may not be 'using' the database in question.

SQL Backup requires a connection to issue a backup command, so it will be blocked by the above user (at least until the user disconnects).

Might be useful to have SQL Backup run a user-defined TSQL command before and after processing ...

jgoemaat

Just to follow-up, setting the database to single_user mode then immediately back to multi_user mode seemed to fix the issue for now. Thanks again!