July 17, 2008 at 7:59 am
Our app support team applies data fixes to the PROD environments now and again. The fixes are run through DEV and STG first, analyzed and validated. However, even with the testing done beforehand, I still don't like running ad hoc updates to production systems without a nimble rollback plan in place. And by nimble, I mean not having to restore a full backup to another server just to pull a table or two out.
One thing I've done before is to export tables and import them back to the same database, appending a date/time stamp to the table names. That way if something goes wrong, I can blow away the original tables and rename the copy tables back to the original names. Or I can just select from the copy tables back into the original tables as necessary.
What are the drawbacks to this? These particular apps (PeopleSoft) don't use constraints so selecting from the copy into the original should be easy enough. But I haven't tested the overhead this operation has on the database. I've never had to do this in production yet and I don't have a full grasp on the impact this type of work would have. I will do some testing to understand what happens to locking, overhead, indexes, statistics, etc. But I'm interested to get the opinion of others.
How do you handle this scenario?
July 17, 2008 at 8:04 am
Because I have not had to work on VLDB's and restores never took more than about 15 minutes I always did full backups and restores.
You might want to look into placing these tables on their own files and filegroups and doing file/filegroup backups and restores.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 8:51 am
That is an excellent suggestion--however, each data fix case is different and involves different tables so there isn't any way I can anticipate which tables out of several thousand to backup.
When we know a fix has to go in, I want to be able to take a few minutes and, without negatively impacting the db, make a quick backup of those tables and have a good rollback so production availability is not affected.
Thx,
Rob
July 17, 2008 at 3:16 pm
I think your method of exporting or copying a table before modifying it is the quickest and easiest method. It's what I do if I'm making table schema or data changes.
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply