Sql-server – Why is the auto-restore procedure not dropping the databases when I expect it to

sql serverstored-procedurest-sql

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?

Best Answer

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://docs.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

Related Solutions

Sql-server – the undo pass that SQL Server does during a RESTORE WITH STANDBY operation

why are those operations on the log file if they haven't been committed in the first place

All operations, committed or uncommitted, are written in the log. A commit ensures that all log entries are made durable (flushed to disk), but nothing prevents uncommitted entries from being flushed before that (either because a log block fills or because another transaction commits, thus forcing the flush for every transaction). A rollback must analyze all the transaction entries and generate compensating actions (for every insert do a delete, for every delete do an insert, for every update do an update that reverses the data back). These compensating actions are, of course, logged.

When a database is recovered it must rollback any transaction that is not committed in the log. It must as such analyze the log, figure out the uncommitted transactions, and then generate compensating actions for any all actions belonging to uncommitted transactions. Online recovery will write the compensation actions into the log itself. Standby recovery will write the compensating actions into an alternative stream, thus allowing for further log to be applied form a 'master' source later (this how log shipping standby read only access works).

Before asking any clarification question, please read ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging.

Sql-server – Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

Best Answer

Related Solutions

Sql-server – the undo pass that SQL Server does during a RESTORE WITH STANDBY operation

Sql-server – Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Related Question