1

I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the process, there are several databases restored at any given time, which uses too much disk space for this to work on my larger databases. When the procedure completes, all the databases are dropped and it does not leave anything behind. I have checked and double checked that I have steps inside the cursor to drop the database, but it does not help.

I have this code inside the cursor:

SET @DropCommand = 'IF DB_ID(''' + @CursorRestoreName + ''') IS NOT NULL
BEGIN
    ALTER DATABASE [' + @CursorRestoreName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [' + @CursorRestoreName + '];
END';

EXEC sp_executesql @DropCommand

I added a PRINT @DropCommand statement as well to verify this code block is being executed, and it is.

Since that wasn't working, I added another cursor inside my first cursor to go through the full list of databases to attempt the drop command, to clean up previous restores. This still has the same effect: multiple database restores exist on the server at a given time during the process, then at the end all have been dropped.

Do you have any recommendations that I could implement so that the restored database is actually dropped before moving on to the next restore? Or perhaps suggestions for troubleshooting this?

Improve this question
2
  • 3
    Change the SET SINGLE_USER WITH ROLLBACK IMMEDIATE to SET OFFLINE WITH ROLLBACK IMMEDIATE. No need to add any wait. This will work 100%. If you have single user mode and if a background process like sql agent thread connects to your db, your drop will fail or you will encounter blocking.
    – Kin Shah
    Sep 12, 2019 at 17:28
  • @KinShah it usually works, but there are times where your deadlock priority prevents it, so I always SET DEADLOCK PRIORITY 10 first too.
    – S3S
    Sep 12, 2019 at 18:49

2 Answers 2

Reset to default
4

What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER. Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user. But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.

While setting the database OFFLINE will work, be aware that dropping an OFFLINE database does not delete the database files. Then you won't be able to RESTORE because the database files are hanging around. And you don't have rights to delete database files, by default, and shouldn't really need them.

RESTRICTED_USER is probably the best state to put a database ahead of dropping it. eg

RESTRICTED_USER

Allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database. RESTRICTED_USER doesn't limit their number. Disconnect all connections to the database using the timeframe specified by the ALTER DATABASE statement's termination clause. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

use master
alter database foo set restricted_user with rollback immediate
drop database foo
Improve this answer
3
  • I will try using RESTRICTED_USER instead of SINGLE_USER to see if it helps. I don't think the issue is that someone else is stealing the single connection, though. The database still drops, it just waits a long time to do so. So the drop command is getting sent.
    – SQLDoug
    Sep 12, 2019 at 21:17
  • I tested this out and the databases are still sticking around until the end of the procedure. They are not being dropped before the next database restores.
    – SQLDoug
    Sep 12, 2019 at 21:27
  • Can you add a complete repro to your question?
    – David Browne - Microsoft
    Sep 12, 2019 at 22:09
0

Several years later, I figured this out. It's one of those facepalm moments; one simple missed command caused me to look into this issue several times over the years, but it was never a high enough priority to spend much time on it. This time, I found it and fixed it.

As I mentioned in the question, I am using a cursor to loop through the databases and run the restore commands. In the cursor, I build the restore command based on the files that need to be restored, and I store that string in a VARCHAR variable. Then I use EXEC to execute that string, and then after verifying the restored database, I drop it. The problem with my code was that at the beginning of the cursor loop, I was not resetting the command string variable to '', so I was appending the next database's restore commands to the existing command from the prior loop. So by the last loop of ten databases, the command was restoring all ten databases before eventually running the DROP DATABASE command.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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