Can you recover data from transaction log?

  • BlackGarlic

    Ten Centuries

    Points: 1091

    Hi, I have question regarding data recovery.

    Yesterday, our Navision consultants accidently deleted important business data from our database. We were gonig to just roll back to our nightly backup data, but if we do we may lose all of our transaction data between backup and deletion time. Is it possible to recover this datafrom transaction log? If so, how can we do this? if not what would be the steps to recover this deleted data?

  • Greg Charles

    SSC-Forever

    Points: 45403

    Assuming you have a full backup from the previous night and log backups to the point-in-time when the deletes occurred, restore the backups to a new database and use T-SQL or SSIS to pull out the data that got deleted and reinsert it into the original database.

    If you have one of the numerous log reader tools from ApexSQL, RedGate, etc., you could construct insert statements from the T-log. If not, you'll have to use the above method.

    Greg

  • BlackGarlic

    Ten Centuries

    Points: 1091

    Thanks for the answer Greg.

    So basically,

    1. Go to SSMS > Management > SQL Server Logs

    2. Use filter to find the data deletion point and time.

    3. Export csv or txt file.

    4. Create blank database with exported file.

    5. Use SQL command / or SSIS to extract the data we need.

    6. Then restore

    Am I correct on this?

    Once again thank you for your advice.

  • matt stockham

    SSCrazy Eights

    Points: 9892

    No, you are a little confused. Greg meant :

    1) take the last full backup and restore it as a new database with norecovery

    2) apply all the log backups from the full backup point to the point just prior to the data being deleted (look up RESTORE in BOL, specifically STOPAT).

    3) the deleted data will be in the new database, pull out the appropriate rows and insert them to the orginal database.

    Matt.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Don't forget the last step:

    4) Fire Navision consultants who accidently deleted important business data from our database.

  • BlackGarlic

    Ten Centuries

    Points: 1091

    Thank you all for your help.

    Another question :(. Is it possible to extract deleted data from transaction log file?

    My understanding is that I have to purchase some third party software.

    Thank you.

  • Ian Yates

    SSCoach

    Points: 19738

    How many extra transactions are there in your db after the delete happened?

    Might it be easier to

    1. Run some transaction detail reports in your db as it stands

    2. Do the log restore using STOPAT to stop just before the delete

    3. Redo the extra transactions by hand

    ?

  • SQLBill

    SSC Guru

    Points: 51440

    Yes it is possible to retrieve the information from the transaction log. As one poster stated you have to buy software specifically designed to do that. Lumigent LogExplorer is one of them. Some have to be installed and running before you need them. I have never used any of them, so I can't give you advice on what will work and what won't.

    Learning experience. It is a very good practice (too late now though) to do a full backup of a database before allowing ANY changes to be made to it. ESPECIALLY by vendors. And don't trust them to do the backups.

    -SQLBill

  • BlackGarlic

    Ten Centuries

    Points: 1091

    Thank you all again.

    As for the transactions, it is more of an old data import rather than live transaction.

    Problem is when we imported those old transaction date, it took good 300 hours. By the way, this incident happened on Wednesday 7 pm and if our oldest log file's time stamp is Wednesday 10pm, is this mean we have to start entering these information again??

    Our Navision consultant thinks it's safer to restore data from Navision rather than SQL Server and I absoultely have no idea what he's talking. Currently he is asking me to extract deleted data from

    the log file, generate csv file, then give him that data so he can enter it back into Navision. :ermm:

    Now I tried ApexSQL Log and SQL Log Rescure, and looks like these might do the job. But I couldn't just get the raw data. It created TSQL commands. Another thing I found is that if the column is updated, looks like I won't be able to get data back with these tools.

    Anyway thank you again for your help 🙂

  • Greg Charles

    SSC-Forever

    Points: 45403

    I only have experience with Lumigent Log Explorer, but I think all the tools work the same way. They generate reversing DML statements from the "before" and "after" data in the transaction log. So they generate a DELETE to reverse an INSERT, an INSERT to reverse a DELETE, and an UPDATE using the "before" values to reverse an UPDATE. The tools don't actually recreate the rows of data as they were before.

    Greg

  • ianT

    SSCrazy

    Points: 2896

    Hi

    If it took 300 hours to import, you must have a big database.

    One consideration for any restore as suggested above should be to check if you have enough space for the new database.

    Good luck

  • Vivien Xing

    SSChampion

    Points: 12297

    Is it supposed to have a tail-log backup immediately after the accident?

    Check books online “Performing a Complete Database Restore (Full Recovery Model)”.

    Additional thoughts to prevent it happening again:

    Why could Navision consultants accidentally delete important business data? Are they supposed to have permission to do the deletion? If any change on your production business data, what is the procedures to follow? Is the deletion script verified/approved? Any verification between “begin tran” and “commit tran” for deletion?

    Is the back out plan ready for any change? Is your full/T-log backup strategy good enough to recover this kind of damage?

    If this needs to be brought to the table of management meeting, you need to have at least these answers ready.

  • as0917041

    SSC Enthusiast

    Points: 127

    SQLBill - Friday, February 15, 2008 4:49 PM

    Yes it is possible to retrieve the information from the transaction log. As one poster stated you have to buy software specifically designed to do that. Lumigent LogExplorer is one of them. Some have to be installed and running before you need them. I have never used any of them, so I can't give you advice on what will work and what won't.Learning experience. It is a very good practice (too late now though) to do a full backup of a database before allowing ANY changes to be made to it. ESPECIALLY by vendors. And don't trust them to do the backups.-SQLBill

    Thanks for the reliable solution.

  • Johnson Welch

    SSCoach

    Points: 18748

    You can recover your deleted data from transaction logs. Here are the step by step process to recover your records from logs:
    https://dzone.com/articles/recover-deleted-data-from-sql-table-using-transact

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply