How to manage database changes with backup information.
-
Hi,
Our company has a database with 7 years of information and now it's extremely heavy for the server. We want to implement a policy to backup the database, extracting 4 years of history from the production database. Now we want to know how to manage the backup if we change the database structure. If you have any suggestion, please send it.
-
You have no backups!??
The only way (I know of) to backup segments of a database will mean a change in your database architecture. First of all you will need to create database files. For simplicity, a history file and a current file.
You will then need to split the table(s) into history and current based on transaction date and place these tables into the seperate files. Once you have done this, you can then backup/restore those files and therefore, historic or current data as and when you need.
Hope that gets you on the right track.
-
You might consider tools like Red Gate's SQL Compare which compares the structure of two databases and allows you to synchronize them, and possibly their SQL Data Compare which allows you to identify new, missing and changed rows even down to the table level.
They have an API available (we use it as part of a .NET program we wrote to update clients to the latest schema of our application database) which allows you to automate the tasks. Alternatively, if you only worry about the information on a periodic basis you could manually deal with it.
If all of the history databases are the same "old" schema, you can simply compare the systems and have SQL Compare generate the SQL script to alter the schema. Then apply the script to any of the old databases as part of a scheduled task. You would only need to regenrate the script if you changed your schema again. Of course that assumes that all the old ones are the same schema. Another nice benefit is that SQL Compare and SQL Data compare can work against 7.0, 2000 and 2005 databases so you can compare a database on an old server to a database on a new server. There are other features like snapshots, but I've neverr needed them so I don't know if they would help.
Good luck!
-
I think you want to manage schema changes so if you need to restore old information, you're covered.
Any scheme you come up with will have the possibilty of error. Or missing something, so here's what I'd suggest.
Build a 2nd database. Use the current schema and then copy out the old information to this database. Take a backup and put a few copies aside. You can delete the 2nd database. Now you have backups of your old information with the scheme. If you need to, you can restore it.
You can do the same thing every year. If you had to go back in time, you might end up with 2 or 3 databases to restore with different schemas and querying might be hard, but you'd have the information.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply