Database stuck in single user mode while restoring

Question

Post reply

Database stuck in single user mode while restoring

Yusuf Bhiwandiwala

Ten Centuries

Points: 1298
More actions
July 4, 2017 at 8:04 pm

#402246

Hi,
We have a test database server instance with multiple databases on it.
We restore a few of these databases on a daily basis from production full backups. We use SQL Agent to schedule and run these restores.
Since there are a lot of active connections to these databases throughout the day, I set the database being restored to SINGLE_USER mode just before executing the RESTORE command on it.
USE Test1DB; ALTER DATABASE Test1DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; USE master; RESTORE DATABASE Test1DB FROM.........; ALTER DATABASE Test1DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
The problem I am facing is that there are clients who are very hungry to get a database connection immediately upon disconnection.
So, in between setting to SINGLE_USER and the RESTORE command, someone else takes the single_user connection, and the RESTORE fails with the error:
Database 'Test1DB' is already open and can have only one user at a time.
When this happens I have to manually login as sysadmin, find out the spid (from sysprocesses) that has the single user connection to the database, kill it, and then try setting it to multi-user. Sometimes that does not help as well and I have to use DAC to do the same.
My Question: What can I do in the restore script so that the session running the RESTORE grabs the single user connection and prevent other hungry clients from taking it just before the RESTORE statement?
Appreciate your suggestions. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Since you're overwriting it anyway, just set it offline instead of setting it to single user.

Cheers!

Kevin3NF SSCarpal Tunnel Points: 4765 More actions · Answer 2

Just for kicks, try it without one of the semi-colons:

USE Test1DB; 
ALTER DATABASE Test1DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE Test1DB FROM.........;


ALTER DATABASE Test1DB SET Multi_USER WITH ROLLBACK IMMEDIATE;
--Shouldn't this be multi instead SINGLE as you posted?

------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NF DallasDBAs.com/Blog Why is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 3

Thank you.

I will try to use this. I think this would solve the issue.

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 4

I will test it just for the kicks and post back the results. I'd be surprised if omitting semi-colon mattered in this case.

Yeah sorry the ALTER DATABASE after the RESTORE is supposed to be MULTI_USER. Ctr-C - Ctrl-V error.

Kevin3NF SSCarpal Tunnel Points: 4765 More actions · Answer 5

Another option is to set it to Restricted_user instead of Single.

The semi-colon is a statement terminator...some connection might be getting in between statements is my thinking. I had a similar issue and this did work for me....or I just got lucky.

------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NF DallasDBAs.com/Blog Why is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 6

Kevin3NF - Wednesday, July 5, 2017 8:23 AM
Another option is to set it to Restricted_user instead of Single.
The semi-colon is a statement terminator...some connection might be getting in between statements is my thinking. I had a similar issue and this did work for me....or I just got lucky.

I would just disable the user IDs for the duration of your restore. Also, reset the admin password to something else.
Cheers

DBASupport

Kevin3NF SSCarpal Tunnel Points: 4765 More actions · Answer 7

Yusuf Bhiwandiwala - Wednesday, July 5, 2017 8:00 AM
I will test it just for the kicks and post back the results. I'd be surprised if omitting semi-colon mattered in this case.Yeah sorry the ALTER DATABASE after the RESTORE is supposed to be MULTI_USER. Ctr-C - Ctrl-V error.

Any luck?

------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NF DallasDBAs.com/Blog Why is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]