In my tests, i have the following script:

USE master;

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).

  • 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

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.

  • 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

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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