SQL Server database mistakenly deleted data retrieval

remember SQL Server database mistakenly deleted data back

Yesterday colleagues in this machine to clean up the database table, connected to the production machine, mistakenly deleted more than 20 tables, fortunately, the night overtime to delete, the production machine was a day a backup, restore backup is the last strategy, the most critical or to retrieve data.

The production machine environment is server2008 R2, sqlserver2012, use DELETE statement to remove the performance at the beginning of the restore, restore can refer to Dudu this article (link), which uses the Recovery for SQL Server tool to restore , found the value of the restored data field is demo, so the final use of SQL command to restore, with the SQL command, Microsoft's official comparison details (link).

First, restore the required condition settings

The use of the command is to restore through the transaction log of SQL Server and a full backup of the database before it is mistakenly deleted , so in the Sqlserver2012 Maintenance Plan Wizard, to establish a full backup, differential backup, and transaction log, as follows

As well as in the database properties, option settings, set as full backup, specific as

Do a good job like the two settings, the database deleted after the deletion of data will be very easy, now say how to restore SQL Server data to the point of failure.

Second, restore command

The restoration is mainly divided into four steps:

1, after the failure, the first to execute the Backup transaction log command, where AdventureWorks as the database name. The command is as follows:

12	BACKUP LOG AdventureWorks TODISK = ‘C:\SQLServerBackups\AdventureWorks_transcationlog.bak‘    WITHNORECOVERY;

2. Restore data from a full backup

RESTORE  DATABASE [qasupervision] from disk=' M:\Database\OA\AdventureWorks_Fullbackup_2014_03_18_010002_ 0155764.bak '   with

3. Restore data from a differential backup

RESTORE DATABASE [qasupervision] from disk=

4. Restore data from the transaction log before restoring to a point in time

1234	DECLARE@dt datetime   SELECT @dt=DATEADD(HOUR,-16,GETDATE())   select @dtRESTORE LOG [QASupervision] FROM DISK=‘C:\SQLServerBackups\AdventureWorks_transcationlog.bak‘  WITH[email protected],RECOVERY

5. Restore the database and execute this command if the database hint is being restored.

1	RESTORE DATABASEAdventureWorks WITHRECOVERY

SQL Server database mistakenly deleted data retrieval