1

My Database has Full BackUp Plan on Every Morning on 01:00 Am. Today I came to work and Deleted all of the Records in a table by mistake in sql server management Studio by running this command

delete * from Table1 where 10=10

instead of

delete * from Table1 where Id=10

this happened around 13:00 pm. worktime starts from 8:00 am . is it possible to undo the deleted Records? if I want to recover it from the backup file, I loses the records entered between 8:00 and 13:00. what is the best solution for this problem?

I read this post Recover deleted records but it was not the same as mine. and I read it was a duplicate post. but again i couldn't find the the main post for this problem.

Improve this question
3
  • 1
    Gah! I assume your transaction log is operating normally? If so, you can do a point in time restore using your transaction log and the full backup. Microsoft has a good write up about it at their site, I assume you are using SQL Server? Use their site if you are: technet.microsoft.com/en-us/library/ms190982(v=sql.105).aspx
    – blobbles
    Commented Aug 3, 2014 at 4:52
  • Oh - this will only work if you are using the Full/Bulk logged recovery mode.
    – blobbles
    Commented Aug 3, 2014 at 4:54
  • In the future, always put those sorts of commands within TRANSACTIONS. That way you can roll back the transaction if you have done something wrong.
    – blobbles
    Commented Aug 3, 2014 at 4:54

2 Answers 2

Reset to default
2

That's called a Major Whoopsie.

Assuming you didn't use transactions around the delete you are stuck with scanning the log files and redoing all the INSERT and UPDATE queries. If you don't have a query log then it's manual entry time.

If it's any consolation the Government of Alaska had a similar issue a while ago, further compounded by the backup tapes not being readable. You've probably lost a morning, they lost the whole thing.

Improve this answer
3
  • If transaction is committed and virtual log file which was part of transaction is reused and overwritten even reading log file would not help. Also reading log file using fn_dblog is not recommended practice in Prod env.
    – Shanky
    Commented Aug 3, 2014 at 14:11
  • It's also not recommended to run DELETE without a well-considered WHERE clause. His options are limited here, best practices can be summarily tossed out.
    – paul
    Commented Aug 4, 2014 at 1:46
  • This is not always true if a table has contraint and Identity and you want to remove all records you have to use delete trucate would have after effects.
    – Shanky
    Commented Aug 4, 2014 at 9:17
0

Do you have log backups or differential backups ? I did not find any information about them in your post. Since(I assume) you do not have transaction log backup point in time recovery is not possible and you cannot recover data changes done after 1 AM. Since your query executed in AUTO COMMIT mode its possible that transaction has committed and changes being made.

From now now on please schedule differential backups if database has simple recovery model and if database has full recovery model schedule trn log backup

Improve this answer

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