Recovering from accidental deletion of data in SQL Server is not a difficult task to recover from the transaction log. However, this recovery requires two prerequisites:
1. There is at least one full backup of the database before it is mistakenly deleted.
2. The recovery model of the database (Recovery mode) is "full".
There are three scenarios for both of these prerequisites:
Situation one, if both prerequisites exist, the SQL statement can be restored in just three steps (refer to the article) without the need for a third-party tool (Recovery for SQL Server).
A) Back up the transaction log for the current database: BACKUP log [database name] to disk= N ' backup file name ' with NORECOVERY
b) Restore a full backup before deleting it: Restore database [DB name] from DISK = N ' full backup filename ' with NORECOVERY, REPLACE
c) Restore the database to a point in time before it was mistakenly deleted: Restore log [database] from DISK = n ' first step of log backup filename ' with STOPAT = n ' mistakenly deleted before the point in time ', RECOVERY
Situation two, if the 1th precondition does not exist, the 2nd precondition exists, need to use third-party tool.
Situation three, if the 2nd precondition does not exist, cannot recover. Therefore, be sure to set the database recovery mode to full.
Recovery of mistakenly deleted data in SQL Server
