Tell me more ×
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.
up vote 1 down vote favorite

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?

share|improve this question
You can use implicit transactions to execute processes that are not sure they are correct, if you realize that something is wrong, you undo and if all goes well you commit. Information on how to use it: SET IMPLICIT_TRANSACTIONS – GeekZero Jan 8 at 10:18
Sorry,b ut HOW large is your database? My last larger one had 21tb and we have zero problems making backups. – TomTom Jan 8 at 10:33
1  
I voted to move to dba.se, not to close... – Remus Rusanu Jan 8 at 11:31
Hmm a transaction is indeed one option, but I thought not all constructs are supported within a transaction. If everything is supported (including creating tables, altering them, etc) I don't see why this wouldn't work. BTW: I suppose I need an explicit transaction then, because nesting implicit and explicit transactions seems to give problems. – Stefan de Bruijn Jan 8 at 11:35
Sorry, was not intended as off-topic. As for @TomTom 's question: the issue is that there's not enough disk space for a full backup, not that the full backup itself is impossible. – Stefan de Bruijn Jan 8 at 12:57
show 3 more comments

migrated from stackoverflow.com Jan 9 at 13:03

1 Answer

up vote 3 down vote accepted

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?

share|improve this answer
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 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 – Remus Rusanu Jan 8 at 11:47
Additionally, if you revert to a snapshot, all fulltext catalogs will be dropped and need to be rebuilt. – StrayCatDBA Jan 13 at 9:15

Your Answer

 
discard

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