Recovering individual records

[zephyran]

Is there a way to use rollback and/or the transaction logs to restore individual records, or parts of records, that might have been accidentally deleted or altered via Management Studio?

We are rolling out a new SQL Server 2005 infrastructure to replace an old, kludgy Advantage database to run a production line. The database is used primarily for traceability of our product. The current Advantage system has a "Show Deleted Records" function that allows us to see any records that have been recently deleted, and to Duplicate them so we can recover the data therein.

Scrapping and/or rebuilding good products is unacceptable, so in case a record is deleted by accident, we need to be able to recover that record on the fly without having to do a full restore of the entire table/database. Is this possible?

 

[onpnt]

Typically you would use a trigger on the table(s) to log the delete transaction to a log table. That would be a method to keep a running log in a separate location within or outside the database. If you can prevent performance issues with this method it is a quick way to restore down to the record.

To recover using the transaction log without third party tools you can restore in several different ways depending on your backup plan. The most basic restor method would be to restore the database to a temporary location and apply the transaction log backups. Then get the records out of the temporary restore and move them back into the production database.table



_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[onpnt]

BTW...this should be directed to forum962 for further questions of this nature

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[zephyran]

Thanks very much for the info! I will run it past my bosses and see what they say.