SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backup


Transaction Log Backup

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14873 Visits: 14396
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
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4797 Visits: 9108
Since I am often the confused person asking questions, when I finally get to help, I sometimes give extra info "just in case".



Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14873 Visits: 14396
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 :-P Hehe

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1547 Visits: 3665
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search