April 24, 2013 at 12:07 am
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????.....
April 24, 2013 at 12:13 am
What recovery model?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 12:25 am
April 24, 2013 at 3:20 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 4:24 am
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??....
April 24, 2013 at 4:46 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 5:05 am
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??
April 24, 2013 at 5:18 am
Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 5:50 am
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
April 24, 2013 at 6:15 am
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??....
April 24, 2013 at 6:33 am
vinu512 (4/24/2013)
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??....
yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.
Regards
Durai Nagarajan
April 24, 2013 at 5:39 pm
guys am a new dba , just leanring, l will like to pos , what section can l do my post
April 24, 2013 at 10:42 pm
durai nagarajan (4/24/2013)
vinu512 (4/24/2013)
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??....
yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.
Thanks for the tips Durai.
April 24, 2013 at 10:44 pm
fadewumi (4/24/2013)
guys am a new dba , just leanring, l will like to pos , what section can l do my post
Hi, Welcome To SSC.
The Forum menu is very self descriptive.
For SQL Server 2005 issues you can post in the SQL Server 2005 section....there are different links for administration and TSQL, depending upon ur requirement you can post in any of them.
Similar, is the case for SQL Server 2008.
April 25, 2013 at 1:02 am
GilaMonster (4/24/2013)
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.
Hi Gail,
One small doubt that is still bugging me is that when i see the size of the log, of the newly restored db, it still shows 17 GB.....if it does not have any transactions before the time it was restored then what does the 17 GB log have??
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply