0

I accidentally deleted all data rows from a important table. But unfortunately, the last backup of the database was done last week.

I have tried several ways, namely by looking at the transaction id and returning it with a query. but from the review I can also be more dangerous because it can eliminate some data

How can I restore the data that I deleted?

Improve this question
4
  • 1
    But unfortunately, the last backup of the database was done last week. So your company accepts 1 weeks worth of data loss? What about Differential or Transaction Log backups? Do you replicate to a DR server? What's your DR plan? What is the maintenance plan on the database?
    – SS_DBA
    Commented Jul 16, 2020 at 0:12
  • If you have all the logs between the last backup and now you can restore the database the point in time and probably recover the data, but it's surgery and you must take the database offline.
    – Bohemian
    Commented Jul 16, 2020 at 0:17
  • sorry, I don't really know about that. I also thought it would be easy with a backup file, apparently I was wrong because the file last week was not in backup.
    – Kevin Turnip
    Commented Jul 16, 2020 at 0:23
  • 1
    dba.stackexchange.com/questions/995/…
    – Aaron Bertrand
    Commented Jul 16, 2020 at 0:30

1 Answer 1

Reset to default
2

It would seem that the before and after states would be recorded in the transaction log. Here is a step by step article on recovering deleted data from the SQL Server transaction log. You can create a db copy from the transaction log right before the delete and get a copy of the data from that point.

 RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\TranLog.trn' WITH STOPBEFOREMARK = 'lsn:0x0000001'

Recover Deleted Data From SQL Server Table by Transaction Logs

Edit: I just saw this solution on another answer. It does not require backups and actually converts the data. It may have some issues with max length datatypes and such, so you would have to check the data.

How to recover deleted data from SQL Server

Which is referred to in an answer in the following question.

How do I get back some deleted records?

Improve this answer
4
  • I have done this query, to get the transaction ID. but when I check based on the delete transaction ID, but the amount of data is greater than the data that I deleted earlier.
    – Kevin Turnip
    Commented Jul 16, 2020 at 0:33
  • "but when I check based on the delete transaction ID, but the amount of data is greater than the data that I deleted earlier." The transaction log is not wrong. Maybe you are at the wrong point? You can try moving forward and backwards and restoring from there. I mean, restoring to a copy. If you see the restored records, you will know when it is right, correct? The correct point with the correct data must be there somewhere.
    – Paul W
    Commented Jul 16, 2020 at 0:49
  • Is there a way to order the query? because Transaction Name, Transaction SID, and also Begin Time is null
    – Kevin Turnip
    Commented Jul 16, 2020 at 4:24
  • ORDER BY [Current LSN] ASC might work... It seems correct in my testing. 013175d3:0000017b:0002 013175d3:0000017b:0003 013175d3:0000017b:0004
    – Paul W
    Commented Jul 16, 2020 at 5:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.