I have a console application that was written to automatically run several long running scripts. Before running the scripts, it will take a database backup by using the
BACKUP DATABASE command. It will then place the database in single user mode:
ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
The application will then begin running the sql scripts. This part all works great. The problem that I am having is if a script fails, we are doing a restore from the backup taken before beginning the scripts. The reason for this is because a transaction rollback will take far longer than simply doing a restore.
I've tried a few different ways:
-works but periodically gets its connections stolen
USE master RESTORE DATABASE databaseName FROM DISK=N'fileLocation'
-doesn't work, database already in use
EXEC master..sp_executesql N'RESTORE DATABASE databaseName FROM DISK=N''fileLocation'''
I've also tried using the .ChangeDatabase() method of the SqlConnection object (VB.NET). This behaves much the same as the first script above in that it periodically will get it's connection stolen.
In code I am taking pains to ensure I never drop/close my connection and watching the Activity Monitor, it appears that the CHECKPOINT process is what is stealing my connection.
The errors I'm seeing are these:
Database 'databaseName' is already open and can only have one user at a time. ALTER DATABASE failed because a lock could not be placed on database 'databaseName'. Try again later. Cannot open database "databaseName" requested by the login. The login failed.
Is there a way to keep CHECKPOINT from stealing my connection?