March 31, 2005 at 8:34 am
Hi all,
We're building a .Net web solution using SQL that has frequent updates of sp's, tables etc. For each update we create a script to alter tables and recreate sp's when needed.
At this moment we always do a full backup of the db before we run this script so that we can go back to a previous version. The problem in doing this is that it will rollback all newly entered data as well. Of course that is not the right solution.
The question is: Is there a simple way to backup the table structure before doing our updates. Something other than just scripting a Drop and Create of a table because then we will obviously delete our data. For example, most of the time we are adding columns to a table by using a script. These columns would then have to be deleted when rolling back.
Thanks,
Victor
March 31, 2005 at 10:38 am
If you make changes to a table then add/modify data, then discover a problem that requires you to rollback the changes to the table, isn't it a pretty safe assumption that the data modifications should be rolled back as well? In other words you want to restore your system to the state it was in just before making the changes. If so, then you really don't need to do a full backup prior to making the changes, assuming your database isn't set to Simple recovery. All you need to do is make sure that you have a good set of backups to include all log backups that have occurred since the last full (or differential) backup.
If all you want is to be able to drop the newly created columns then that is easily scriptable. However, if you are modifying existing columns, the rollback process, while still possible, is just a bit more risky to your data as it might truncate the data in the case where the modification increased the data length and then the rollback shrunk it back down.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply