Retrieve deleted tables in SQL Server
I created a database 10 months ago with 15 people connected to the server and all running the application. Suddenly an error generated in the application that said "invalid object" and table name. I checked the error and was shocked because the tables were deleted.
I don't know why this is happening in running condition and the SQL Server error log file has no clue about it. I lost all my office data. The mdf file is 109 mb and ldf is 519 gb. The database has no tables. Is it possible? This has happened to me three times and every time I changed my server PC with new Windows and SQL Server installations. What should I do?
It sounds like someone is logging into the database and deleting the tables. Take a look at Lumigent's Log Explorer
product. This will allow you to look into the transaction log and see who deleted the tables, as well as possibly roll back the transaction. You'll need to either have not backed up the log, or have the transaction log backups handy.
If your database is in SIMPLE recovery mode, there are no products that can recover the data. You will want to evaluate your databases security model. You probably have granted your users too many rights within the database.
This was first published in December 2007