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