Someone accidentally deleted the table in database

Question

Someone accidentally deleted the table in database. I want to reterive the table and records. Do we have any option? Thanks in advance.

Manas Tripathi · Answer

Here is an example for recovering the data and table from SQL logs https://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/

Manav Pandya · Answer

Hello I have faced this situation before , when i was working one of the it firm Deleted more than 50,000 recors and think about how to retrive , gone through few post but did not get any solution Like few sites suggested to download any tool which help you to retrive tables , but it was hopeless and useless I recommend you to use Transaction before doing any operation , so that it can be rollback Otherwise there is no proper solution found other that backup Thanks

Chirag Khabaria · Answer

@Manav, I replied to @Sankara comment of Do we have any other option, using transaction log? and not on your post.

Manav Pandya · Answer

@chirag I'm not talking about transaction logs , I'm trying to say about transaction which holds our operations status , like we deleted something so we can rollback it , type only transaction in SQL server , than u will get my point, thanks

Chirag Khabaria · Answer

I doubt transaction logs would help as they are mostly used for restoring non-commited transactions due to failure. But with what ever you have mentioned seem the transactions were successful in deletion.

Sankara Krishnan Venugopal · Answer

HI Everyone, I tried this way, it works. https://blog.sqlauthority.com/2011/12/23/sql-server-a-quick-script-for-point-in-time-recovery-back-up-and-restore/ Then created the JOB to take automated database log file in server. @Manav, @Manas and @Chirag, Thank you many much for your helps :)

Manish Kumar · Answer

Do you have backup or some other mechanisam like replication, log shipping in your environment because there is no other simple way to recover deleted table and data.

Sankara Krishnan Venugopal · Answer

Do we have any other option, using transaction log?

Chirag Khabaria · Answer

Only way is probably you have a back of the database.