Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

In my tests, i have the following script:

USE master;
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot'

In some case that I am not able to understand, I get the following error:

Database state cannot be changed while other users are using the database 'TestDB'
RESTORE DATABASE is terminating abnormally.
Changed database context to 'master'.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

It seems the messages are not in the correct order (.NET issue?), but aside from that, what is the situation when I can get RESTORE blocked after SET SINGLE_USER? I would expect SINGLE_USER to give me exclusive access (and it works most of the time).

After this error, the DB is indeed in SINGLE_USER state (but not restored).

share|improve this question
It sounds like there's already an existing connection on the database, blocking your connection. Run sp_who2 to see the spid and kill it before you make your attempt. – Thomas Stringer Mar 20 '12 at 2:51
Yes, I can try to, but how can it even happen? Can it reconnect between KILL and RESTORE? – Andrey Shchekin Mar 20 '12 at 3:50

2 Answers

up vote 3 down vote

When you set the database to single user, it allows a single user into the database. If there's someone else trying to get into the database, and you don't have a connection to the database someone else could get into the database.

share|improve this answer
Can it happen if both these statements are in a single script that is sent to the DB at once? Is it because I am using master? – Andrey Shchekin Mar 20 '12 at 3:45
1  
No, a single script like: "use master; alter database MyDb set single_user; use MyDb" is still a single connection. Something else would need to log into the SQL Server and into that database. It could be a user, a job, an admin. Setting the database into restricted user will block anyone who isn't a member of the db_owner fixed server role from connecting to the database. I'd recommend that, or shutdown the app/web servers while you do the database restore. – mrdenny♦ Mar 20 '12 at 7:01
So I still do not understand why can I get other users are using the database when I have a single script ALTER DATABASE ...; SINGLE_USER RESTORE DATABASE ...;. – Andrey Shchekin Mar 20 '12 at 9:18
1  
@AndreyShchekin Denny explained why. You can guess all day long but a quick query would show you what connection is taking up the single connection of the SINGLE_USER database. – Thomas Stringer Mar 20 '12 at 12:23
up vote 2 down vote

SINGLE_USER will not give you exclusive access per say. It will only allow one connection to the DB, and that could very well be another of your own session. I have seen another scenario where the SQL agent connects to the DB and that takes up the single_user connection.

When I want to prevent user connections to a DB that is being restored over (with replace), I use RESTRICTED_USER.

share|improve this answer
The problem I see is not the SINGLE_USER blocking me from RESTORE, it is Database state cannot be changed while other users are using the database. Wouldn't RESTRICTED_USER potentially cause the same problem? – Andrey Shchekin Mar 20 '12 at 3:49
A SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE will immediately disconnect user and only allow access to SA or db_owner. – StanleyJohns Mar 20 '12 at 3:59
How is it different from SET SINGLE_USER ROLLBACK IMMEDIATE given that current connection is not blocked from asking for RESTORE in either case? – Andrey Shchekin Mar 20 '12 at 4:20
why not do SET OFFLINE WITH ROLLBACK IMMEDIATE then do the restore? – Nick Kavadias Mar 29 '12 at 10:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.