Transactional Backups for archived data
-
Can transaction log backups be used to restore old archived data over newer current data in the database, without effecting newer data in the database. Help in this regards will be highly appreciated.
Also, can other types of backups also be used to solve this operation.
Rattu
- Rattu
-
You have to restore full database backup first and then transaction log backups which created since full database backup in the same sequence.
-
no. Data is not "archived". You'd have to build your own process for this.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
I am archiving the data manually, using some stored procedures. What kind of backups/restoration techniques are required in order to archive date and restore it later on, without effecting existing data in the database.
Counting on you ppl.
Regards,
Rehan Rattu
quote:
no. Data is not "archived". You'd have to build your own process for this.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
- Rattu
-
Talking about Mission Impossible.
Data in the database is usually linked and dependant and any change affects it.
How do you think you can bring archived data back without affecting existing data?
The only way I see this possible is to cut off all dependant data to archive before a specified date when it is no longer needed. If that need arises again, just copy data back since it doesn't directly affect current data.
For anything else you will have to make a special procedure to selectively bring data from archive and possibly overwrite current data. You need a special application for that.
-
jcool has a good point regarding referential integrity, constraints, and such. This could be an issue, but not impossible. As long as Primary keys stay unique and can never be duplicated.
My suggestion for being able to recover the data is to archive it to another table or set of tables. Maybe even in another database. This will allow you to restore it and back it up seperately.
You may also want to think about creating partitioned views.
"Keep Your Stick On the Ice" ..Red Green -
Yes, Using Uniqueidentifer is the "key" to making this work. You could copy the database to a new instance and rename it to Archive*. Create a DTS package to move data back to the original database as needed (using a where clause). You need to identify dependent data so the referential itengrity is consistent.
-Isaiah -
If I were to do this, I would restore the 'archived' data to a new database name. That way I would have two databases, DB1 with the current data and DB2 with archive data. If I needed to run a script to return data from both, I would just use the three part name (database.owner.table).
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply