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 12»»

Read transaction log file Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 12:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Hi,

We have an issue where someone had fired a delete query on a table which did't have any audit triggers on it. The loss of data went unnoticed until now.

We do have backups of the time when that query was fired. It was data relating to a school's session a few years back and now we have some requirement using that data. So, we have to retrieve it...somehow. The table gets truncated every session so we don't even know what data was there originally and what data got deleted.

So, the option left was to read the transaction log from a backup of that session. I trial version of Apex SQL Log for this. But, it doesn't show any transactions from the log. It just displays a screen where it says, "Loading Transaction file"....processes for a while and then just doesn't display anything. When I manually query the transaction log, I can see that it has data. But, Apex SQL Log is not showing anything. Any Ideas...guys????

Or is there any other free tools which could help me get queries that were run on the server from the Transaction Log????.....


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1445776
Posted Wednesday, April 24, 2013 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
What recovery model?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445778
Posted Wednesday, April 24, 2013 12:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Full Recovery Model

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1445780
Posted Wednesday, April 24, 2013 3:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Can you not just take a log backup (or take the regular log backups), restore the lastest full to a new DB, restore all the logs and STOPAT just before the delete happened?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445814
Posted Wednesday, April 24, 2013 4:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Gail, thanks for all the help.
I played around with Apex SQL Log....the software's fine...in fact its very good.

The actual problem is something else. Sql Apex Log is only showing that data which I inserted into a table after I restored the backup on my server. That means its not reading the historical log and is only reading the log after I restored it on my server.

This is confusing me a little. The actual database on the production server was in Simple Recovery Model. So, I am assuming that when you restore a backup of a database that is in Simple Recovery Model the Transaction Log does not show the historical data. It starts from the time after the restore takes place.

Is that true??....


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1445840
Posted Wednesday, April 24, 2013 4:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Hang on, if the database that the delete was run on was in simple recovery model, why did you tell me it was in full recovery?

When restoring a database, all that you get in the log is operations after the restore. There's no reason why any log records from before would be needed and full backups only include the portion of the log over the time of the full backup anyway. That's for any recovery model.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445850
Posted Wednesday, April 24, 2013 5:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Sorry, I didn't check if it was in Full Recovery Model. I was so sure. But some how it is in Simple Recovery model and according to Apex SQL Log, the transaction Log that exists with the actual database is from 18th April 2013.

So, I assume that there is no way of getting the data back....right??


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1445857
Posted Wednesday, April 24, 2013 5:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445863
Posted Wednesday, April 24, 2013 5:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
Hello,

Do you have full backup taken every day , if it is try that before delete and recovery to the max you can.



Regards
Durai Nagarajan
Post #1445878
Posted Wednesday, April 24, 2013 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
GilaMonster (4/24/2013)
Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.


Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1445893
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse