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.

Community
  • 1
nnmmss
  • 157
  • 1
  • 1
  • 10

2 Answers2

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.

paul
  • 129
  • 1
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

Shanky
  • 19,148
  • 4
  • 37
  • 58