Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Transaction Log Backup Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
homebrew01 (2/21/2013)
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".

No doubt. In the scenario asked about we'd be recovering data from a specific table from what would otherwise be considered an 'old' backup of the database, not recovering the entire database to the most recent point-in-time before a system failure. Sorry, I figured that went without saying. Thanks for highlighting that point.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422788
Posted Thursday, February 21, 2013 3:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 2,829, Visits: 8,477
Since I am often the confused person asking questions, when I finally get to help, I sometimes give extra info "just in case".


Post #1422807
Posted Thursday, February 21, 2013 3:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
homebrew01 (2/21/2013)
Since I am often the confused person asking questions, when I finally get to help, I sometimes give extra info "just in case".

and that approach happens to align quite well with the topic of this post


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422810
Posted Friday, February 22, 2013 1:22 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Regarding the "how big should my log file be permanently set to after I back up and shrink it?" question:

I came across a very nice script that will tell you when the most recent database or log autogrow events occurred. You could add this to a daily routine server check Agent job.

It's been useful to me as an alert that a log file blew up b/c of some extraordinary activity, at which point I could decide to (a) do nothing, b/c it will happen again in the future and the new size is OK or (b) take remedial measures.

If you do that, it might give you some piece of mind that, if you guess too small for your log file, you'll at least be notified that an auto-grow event took place. You generally want to avoid those from happening: explicitly set the size of your log big enough so you don't have them.

HTH,
Rich
Post #1423238
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse