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?
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
toSET 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.