February 15, 2008 at 11:15 am
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?
February 15, 2008 at 11:51 am
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
February 15, 2008 at 1:00 pm
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.
February 15, 2008 at 2:11 pm
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.
February 15, 2008 at 2:16 pm
Don't forget the last step:
4) Fire Navision consultants who accidently deleted important business data from our database.
February 15, 2008 at 4:09 pm
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.
February 15, 2008 at 4:28 pm
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
?
February 15, 2008 at 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
February 15, 2008 at 5:36 pm
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 🙂
February 19, 2008 at 8:41 am
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
February 20, 2008 at 2:37 am
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
February 20, 2008 at 9:33 am
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.
August 18, 2018 at 12:08 am
SQLBill - Friday, February 15, 2008 4:49 PMYes 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.
January 9, 2019 at 12:57 am
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply