Comments
5 comments
-
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. -
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? -
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! -
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 ... -
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!
Add comment
Please sign in to leave a comment.
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.
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!