This seems like a fairly common scenario, yet I'm not sure how to do this.

I have a very large database (think a lot of terabytes), which makes it impossible to create a full backup. At some point, I want to run an update script on the database that can do some schema modifications, alter some data, etc.

To be on the safe site, I would like to be able to undo these operations if something goes wrong. It's okay if no-one can access the database during the update process.

To make it a bit more complex, I don't want to ruin the daily maintenance / backup process.

I've been thinking about putting the database in single server mode and then rolling back the transaction log for a specified period of time, but I'm not really sure how to do this (and time doesn't feel really reliable). I've looked at the 'checkpoint' function, but am unsure how to use this. Anyone?


1 Answer 1


Use a database snapshot. Create a snapshot, do your worse. If things go ugly, revert to the snapshot, see Revert a Database to a Database Snapshot. Database snapshots are copy-on-write so they take almost no space on disk (the space taken will grow as you update the original db and the copy-on-write must save the previous data). They're fast to create and drop.

If you have to revert from a snapshot it will break the backup log chain:

Reverting breaks the log backup chain. Therefore, before you can take log backups of the reverted database, you must first take a full database backup or file backup. We recommend a full database backup.

This will impact your maintenance and you must plan accordingly, eg. be prepared to re-seed the log chain with a new full backup. Unfortunately, there is no realistic alternative. Of course, you should test your migration scripts before attempting them on production. You are using scripts, aren't you?

  • Thanks for the information. That is what I was afraid of... Yes I am using scripts, parsing them and executing them from c#. I was actually thinking about a good way to test them, but again, this is more difficult than it seems: I can't think of any way to make sure the SQL file will surely succeed, since the outcome can be dependent on the data. There is of course opportunity for maximizing chance. Specifically: I am considering duplicating the schema to a temporary database, sampling some data in there and testing on that; if that works, it will probably work on the production data as well.
    – Stefan de Bruijn
    Jan 8, 2013 at 11:44
  • Ideally you should test on a restored backup of the original database, on a machine similarly configured. BTW, I happen to have a .sql executor for C#, see github.com/rusanu/DbUtilSqlCmd and/or nuget.org/packages/com.rusanu.DbUtilSqlCmd Jan 8, 2013 at 11:47
  • Additionally, if you revert to a snapshot, all fulltext catalogs will be dropped and need to be rebuilt. Jan 13, 2013 at 9:15

Your Answer

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