February 21, 2013 at 2:08 pm
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
February 21, 2013 at 3:02 pm
Since I am often the confused person asking questions, when I finally get to help, I sometimes give extra info "just in case".
February 21, 2013 at 3:13 pm
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 😛 :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 22, 2013 at 1:22 pm
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
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply