Restore SQL Server's historical data through Logs

Some time ago, I sent an example of restoring MSSQL data through logs.

To restore logs, the most important thing is:

1. A complete backup is required, and the backup must be performed before the data is modified or deleted.

2. Log backup is performed after data is updated and deleted. The log backup is used to restore the previous data.

 

Purpose: To restore the deleted table

Visual operation steps in SQL Server Management:

1. Create a database and complete backup:

-- Create Test Database
Create   Database DB
Go

--Back up databases
Backup DatabaseDBTo Disk='C: \ dB. Bak' WithFormat
Go

 

2. Create an empty table

-- Create test table
Create   Table DB. DBO. tb_test (ID Int )

3. Delete the empty table just created. Suppose the table is deleted by mistake.

-- Assume that the table dB. DBO. tb_test is deleted by mistake.
Drop   Table DB. DBO. tb_test

In this step, we want to restore the deleted tb_test table. Remember the time before the table is deleted, which will be used later.

At this time, you need to back up the log, you can use the SQL management interface to operate backup, you can also use T-SQL backup

Backup   Log DB To   Disk = ' C: \ db_log.bak '   With Format
Go

 

4. Restore the database. You can replace the original database or restore it to a new database db1. here, the new database db1.

For interface operations:

"Task"-"Restore"-"Database ":

On the "General" selection page, select the complete backup: DB. Bak,

Target Database: db1

Under "recovery status", select 2nd items "do not perform any operations on the database, and do not roll back the committed transactions .." Click "OK". The status of the db1 database changes to "Restoring .."

Next, right-click the db1 database and choose "Restore"> "transaction log". Select the transaction log backed up after the table is deleted: db_log.bak,

Select a time point below, which is the time before the table is deleted. Click OK to restore the table. The deleted table is returned.

 

Complete script:

-- Create Test Database
Create   Database DB
Go

-- back up a database
Backup database dB to disk = ' C: \ dB. bak ' with Format
go

--Create test table
Create TableDB. DBO. tb_test (IDInt)

--Delay of 1 second, and then perform subsequent operations (this is because the time precision of SQL Server is up to 3% seconds. If there is no delay, the operation to restore to the time point may fail)
WaitforDelay'00:00:01'
Go

--Assume that the table dB. DBO. tb_test is deleted by mistake.
Drop TableDB. DBO. tb_test

--The time when the table was deleted.
SelectDT=Getdate()Into#
Go

-- After the deletion operation, the database. DBO. tb_test table cannot be deleted.

-- The following shows how to restore the accidentally deleted table dB. DBO. tb_test.

-- First, back up the transaction log (the transaction log can be used to restore to the specified time point)
Backup   Log DB To   Disk = ' C: \ db_log.bak '   With Format
Go

--Next, we need to restore the full backup first (the restoration log must be performed on the basis of the full backup)
Restore DatabaseDBFrom Disk='C: \ dB. Bak' With Replace, Norecovery
Go

-- Restore the transaction log to the beginning of the delete operation (the time here corresponds to the previous Delete time, and earlier than the delete time
Declare   @ Dt   Datetime
Select   @ Dt = Dateadd (MS, - 20 , DT) From # -- Obtain a time earlier than the time when the table is deleted.
Restore   Log DB From   Disk = ' C: \ db_log.bak '   With Recovery, stopat = @ Dt
Go

--Check whether the table is restored.
Select * FromDB. DBO. tb_test

/*-- Result:
ID
-----------

(The number of affected rows is 0)
--*/

--Test successful
Go

--Finally, delete the test environment.
Drop DatabaseDB
Drop Table#

 

This articleCodeRefer to csdn post: http://topic.csdn.net/u/20080321/12/f5d33007-9bea-43f1-844b-914e68de727d.html