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

How do you restore a deleted record/s directly from the SQL transaction log? Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:41 AM
Points: 5, Visits: 153
How do you restore a deleted record/s directly from the SQL transaction log? I would also like to add that the database is huge. So, restoring from a full SQL backup along with the transaction logs is not an option. Also, I am not interested in any third party tools $$$$, unless it is free.

I want to use TSQL to accomplish this mission.
Post #1558464
Posted Friday, April 4, 2014 6:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 885, Visits: 2,468
You can use T-SQL to find in the log where your deleted record is at, but the only way you are going to get it back is from a restore if the delete was already committed.

You can find a walkthrough of using fn_dblog here on MSSQLTips.com.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1558474
Posted Friday, April 4, 2014 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:41 AM
Points: 5, Visits: 153
Hi Shawn, there is no way to restore a record directly from the transaction log? That is too bad, I have a huge database and I do not have the room on my drive to create a duplicate database. I would think that Microsoft SQL team would have some provision (TSQL system stored procedure) that would allow a simple operation like this.
Post #1558590
Posted Friday, April 4, 2014 11:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 23,397, Visits: 32,242
ray santalis-466762 (4/4/2014)
Hi Shawn, there is no way to restore a record directly from the transaction log? That is too bad, I have a huge database and I do not have the room on my drive to create a duplicate database. I would think that Microsoft SQL team would have some provision (TSQL system stored procedure) that would allow a simple operation like this.


What you think is a simple operation really is not. The transaction log isn't meant to be used the way you are attempting to use it. In addition, the VLF's get flagged as inactive when it does not have any active transactions.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1558605
Posted Monday, April 7, 2014 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:41 AM
Points: 5, Visits: 153
Thanks everyone!

I just wanted to make sure.
Post #1559240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse