Actual combat SQL Server 2008 Database deleted data recovery by mistake

Keywords: sql Server, recover deleted records

Today, a friend called me in a hurry, and he mistakenly deleted all the records in the two tables in the SQL Server 2008 database with the DELETE statement, and the database was not backed up before. Let me fix it for him, or he will pay a lot of money to the clients.

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 third-party tools.

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.

I am now facing the second situation and need to find third-party tools.

Start looking for the log Explorer for SQL Server, SQL Server 2008 is not supported.

I later looked for SQL Log Rescue, and SQL Server 2008 was not supported.

Then found is SysTools SQL Recovery, which supports SQL Server 2008, but needs to be purchased, demo version does not have data recovery function.

Finally found on officerecovery.com Recovery for SQL Server, although also commercial software, need to purchase, but the demo version can recover data, as long as the database file is not more than 24Gb. Fortunately, the friend's database file is not big, with it to complete the recovery of the deleted data mistakenly.

Here's a look at the steps to recover with recovery for SQL Server:

1. Running Recovery for SQL Server

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

3. Next > Next, enter the Recovery Configuration interface and select Custom (select Custom to recover 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 you want to recover.

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

6. Click Start to start the recovery operation (generate the corresponding SQL file with the 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 recovered data is stored.

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

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

The next step is to celebrate victory! The best way to celebrate victory is to write a blog!

Actual combat SQL Server 2008 Database deleted data recovery by mistake