March 5, 2009 at 4:21 am
I have a department store database with the following tables:
tbl_branch ---> branch_id, name, location
tbl_category ---> category_id, name
tbl_items ---> item_id, name, price, branch_id, category_id
ISSUE:
1. I have backup files of more than 2 years of my database. (around 6000 .BAK files)
2. I need to retrieve data of the mentioned tables.
3. Note that tbl_category & tbl_branch data have been modified several times by the administrator over the period.
4. I want to save those tables in a separate database from the backup files, so that i can run queries for statistics purpose.
Database engine used: MS SQL 2000 Standard
What process should i follow to automate the task? Are there any tools that can help me achieve this? :crazy:
March 5, 2009 at 7:35 am
Looks to me like about 6000 restores. SQL 2000 does not have object level restores. I believe one or two of the third party tools supports object level restores but that's no good to you at this point.
-- You can't be late until you show up.
March 6, 2009 at 10:19 am
you could automate this. I'd create a new table to store the data you need (or tables) and include a datetime field in this table as well. Do this in a new db.
Create a proc to move the data from the source tables to the new table. Reference some standard db name you'll need for the restore. Put this in the new db.
Write a script that restores a .bak to the standard db name. Call the proc in the new db when it's done to move the data, passing in some date/time value from the restore, then have a drop db command in the script. Or restore over the previous restore. You can overwrite a db in restore.
Loop the script over files.
Not pretty, and depending on the bak size, it might or might not work. Not sure if this is quicker than using SQL Compare to compare the tables to the backup. Not sure if SQL Compare works on 2000 backup files. You could ping Red Gate on this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply