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?
Best Answer
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:
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?