Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Did you try adding WITH REPLACE to your RESTORE statement? –  Aaron Bertrand Sep 9 '13 at 14:11
    
@AaronBertrand I have not tried that. I will thanks. –  TKTS Sep 9 '13 at 16:27

1 Answer 1

up vote 1 down vote accepted

Your script is failing on the ALTER DATABASE not on the RESTORE. Try setting it to RESTRICTED_USER first:

EXEC('
USE master;
ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE '+@db_name+';
ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE '+@db_name+';
');

This script so far has never failed me.

There are two differences to your script. The main difference is that setting the database to RESTRICTED_USER first allows the script to get executed even if the database is in SINGLE_USER to start with.

The second is that it drops the database instead of restoring it. You could try to replace the DROP with your restore directly, but that might open a new "gap" for something else to sneak in. As you are restoring from a file and not from the database-backup-history, dropping the database first should not be a problem for you. Just execute the restore after the database is gone.

share|improve this answer
    
Thank you! I think your two additions are exactly what I need. –  TKTS Sep 9 '13 at 17:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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