The following code creates a user that is able to restore a database:
CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser
This works fine and the user can restore the database, however if I want to set the database to SINGLE_USER
and rollback any existing connections, the restore command is blocked and fails:
Session 1
/* SELECT from a table and leave the SSMS window open, leaving a sleeping SPID */
USE AdventureWorks2014
SELECT * FROM Person.Person
Session 2
EXECUTE AS LOGIN = 'RestoreUser'
ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE
REVERT
Eventually, session 2 times out with
Msg 5061, Level 16, State 1, Line 3
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2014'. Try again later.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
The following sp_whoisactive
screenshot shows that the command is blocked:
The sp_whoisactive
lock report for session 59 shows
<Database name="AdventureWorks2014">
<Locks>
<Lock request_mode="X" request_status="CONVERT" request_count="1" />
<Lock request_mode="S" request_status="GRANT" request_count="1" />
<Lock request_mode="U" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="(null)">
<Locks>
<Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
<Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
The Microsoft Article for SET SINGLE_USER states
To quickly obtain exclusive access, the code sample uses the termination option WITH ROLLBACK IMMEDIATE. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.
and
Requires ALTER permission on the database.
My RestoreUser
account has the correct permissions (dbcreator
server role gives ALTER ANY DATABASE
permissions) but I don't know why the command is blocked, the first quote suggests all other connections would be disconnected.
I also granted ALTER ANY CONNECTION
to RestoreUser
but that did not help.
What I found did fix the problem, is if RestoreUser
is a user in the database being restored (doesn't need any permissions within the database)
CREATE LOGIN RestoreUser WITH PASSWORD = 'ABC@123'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser
USE AdventureWorks2014
CREATE USER RestoreUser
The code
EXECUTE AS LOGIN = 'RestoreUser'
ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE
REVERT
Then runs without being blocked by the sleeping session.
My questions are
- Why does the
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
code get blocked when it should kill and rollback all open SPIDs - Why does creating the user in the database fix this issue?