SQL SERVER – Who dropped what object at what time ?

Last day of the last year, I came across a situation where I was stuck for a while even though we had backups. However later on found a solution and felt so excited ;).
Note : Please do not use this script for any negative purpose.

Problem :
As I mentioned earlier that last year on 31st Dec 2013, we were busy helping our client for yearend closing and suddenly we got an error from year closing procedure. Given below are the details.Msg 208, Level 16, State 1, Line 1 Invalid object name ‘tbl_Yeartodate_Value’.

Now, the problem is this table gets updated every evening and we do not why it is not available in the database. We do have each and everyday backup. However, we do not know when it was dropped and who dropped it. We started looking for the solution because it is not possible to restore all backups and check one by one.

Solution :
Some of our support engineers proposed the third party solution to recover the table directly because we do not know in which backup this table exists. I bought some time from the client and started doing my development for recovery and after few hours, I developed a script that could give the dropped object name along with the dropped date and dropped by name as well. However, I made sure that the client will not take any action against the person who dropped it.

Blog Stats

Follow Blog via Email

Disclaimer

This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.