• opc.three (2/21/2013)


    midnight251 (2/21/2013)


    Interesting. I didn't know you could take it down that far and only restore certain data to certain tables.

    You can't restore data for specific tables without the help of some third-party tools. What you would have to do is restore the relevant FULL backup taken prior to the DELETE and then restore each log backup from that point forward one at a time WITH STANDBY, checking for the missing data, and then when it first disappeared from the database you'll have identified which log file logged the DELETE operation. You would then need to repeat the process starting with the FULL backup and all the logs minus the last one you restored...and then you could restore that last one with the STOPAT option to get as close to that point-in-time before the delete occurred. Of course I am describing a nightmare scenario for a DBA, one I've had to do but where I pretty much knew within a few minutes when the data was deleted so it wasn't a huge deal, but some folks are not so lucky.

    In a situation like this, you restore your database to a temprary database so as not to affect your current live database. So you get your full backup and restore it as "MyProductionDatabaseName_TempRestore" or some such. When you have it restored from the correct time, copy the missing table data from "MyProductionDatabaseName_TempRestore" to "MyProductionDatabaseName".

    As for all your transaction log backups, there's a setting in the Maintenance Plan for how long you want to retain the log backup files. 10 -14 days is fairly common.