How SQL Server 2008 database mistakenly deletes data to recover data _mssql2008

A friend called me in a hurry, and he accidentally deleted all the records in the two tables in the SQL Server 2008 database with the DELETE statement, and there was no backup before that database. Let me help him out, or he will pay a lot of money to the client.

Recovering data incorrectly in SQL Server is not a difficult task, and can be recovered from the transaction log. However, this recovery requires two prerequisites:

1. There is at least one complete backup of the database before the deletion of the error.

2. The recovery model for the database (Recovery mode) is full.

For these two prerequisites, there will be three different conditions:

If both prerequisites exist, the SQL statement can be recovered in only three steps without recourse to Third-party tools.

A) Back up the transaction log for the current database: BACKUP log [database name] to disk= N ' backup filename ' with NORECOVERY

b Restore a full backup before the deletion: Restore database [DB name] from DISK = N ' full backup filename ' with NORECOVERY, REPLACE

C Restore the database to the point before it was mistakenly deleted: Restore log [database] from DISK = n ' first step log backup filename ' with STOPAT = n ' mistakenly deleted before point of time ', RECOVERY

Second, if the 1th prerequisite does not exist, the 2nd prerequisite exists and requires the help of a third-party tool.

If the 2nd precondition does not exist, it cannot be recovered. Therefore, be sure to set the database recovery mode to full.

I now face the second situation, need to find third-party tools.

The log Explorer for SQL Server was started, and SQL Server 2008 is not supported.

The SQL Log Rescue was later found, and SQL Server 2008 was not supported.

The next thing to find is SysTools SQL Recovery, which supports SQL Server 2008, but needs to be purchased, and the demo version does not have data recovery capabilities.

Finally found on the officerecovery.com recovery for SQL Server, although also commercial software, need to buy, but the demo version can restore data, as long as the database file does not exceed 24Gb. Fortunately, a friend of the database file is not large, using it to complete the recovery of mistakenly deleted data.

share the following steps for recovering with recovery for SQL Server :

1. Run Recovery for SQL Server

2. Click File > Recover in the menu to select the data file (. mdf) of the database to be restored

3. Next > Next, enter the Recovery Configuration interface and choose Custom (choose Custom to restore the deleted data from the log).

4. Next Enter the Recovery Options window, select Search for deleted records, and select the log file path for the database to be restored.

5. Next and select the target folder (destination folders) to store the SQL statements and bat files generated during the recovery process.

6. Click Start to start the restore operation (generate the corresponding SQL file and bat file in the destination folder selected in the previous step), and then the SQL Server Database Creation Utility window appears.

7. Next, select the target database where the restored data is stored.

8. Next, select Import availiable data from both database and log files

9. Next, Next, and then complete the recovery of the data!

The above is the entire content of this article, I hope to help you learn.