Restore the SQL Server database from the log to a point in time

server| Recovery | data | database
Author:david Euler
Date:2004/10/16
email:de_euler-david@yahoo.com.cn

Have any questions, please contact me:



DB2 can enable the database to revert to a specified point in time, when the SQL Server database recovery model is full or bulk copy, you can recover the database from the log. In fact, a single Transact SQL statement recorded in the log will redo these SQL statements when the database is restored.

Prerequisite: Mybbs is a table in database test,

Database test recovery model is Full,auto Close,auto shrink two options are not selected.

The data files and log files for database test are the default automatic growth states.



a:2004/10/13,16:00 for database backup, backup DB test to disk= ' D:\db\1600.bak ' with init

b:2004/10/14,13:00 the database to Update,delete and other operations;

When c:2004/10/15,18:00 uses the delete Mybbs where id>300, the statement mistakenly writes the delete Mybbs, thus deleting all the data in the table Mybbs.



Now at C Point, C points to the database, we want the database to be able to restore to the State before C, such as the return to the October 15 17:59 minutes of the state.

To recover the database B point, use the database 1600.bak for a-point preparation, and use the log backup to be the latest in the standby 1820.logs, and thus do the following:



--Standby log:

BACKUP LOG test to disk= ' d:\1820.logs ' with INIT



--Restore the database 1600.bak and use the WITH NORECOVERY parameter:

RESTORE DATABASE test from disk= ' D:\db\1640.bak ' with NORECOVERY



--Use log recovery database until 17:59 October 15:

RESTORE LOG Test
From disk= ' d:\1820.logs ' with recovery,stopat= ' 10/15/2004 17:59 '




The above three transact the corresponding process of the SQL statement:

1. Restore the database to point A;

2. Perform log logs between A-b and restore the database to point B.





This restores the database to the specified point in time. If the recovery is unsuccessful, the possible causes are: 1. The correct standby database is not used; 2. The database option selects Auto Shrink.