How to recover SQL Server data from a log

This is the time to read about recovering data through logs and point-in-time in SQL Server. Also looked at some online examples to see how to recover data through the log.

Prerequisites:

Database recovery to a non simple mode, remove automatic shutdown and auto shrink two options

If this is a simple pattern: a statement like the one below will not be logged in the log: SELECT * into t from [table name]

To ensure the integrity of the data, the recovery model of the database is changed to "complete"

Test environment:

1, set up database and test table

Create DATABASE ZP

CREATE TABLE [zping. com] (

ID int,

Name varchar (20)

)

To insert test data:

insert INTO [zping.com] (Id,name)

VALUES (1,ZPING.COM1)

insert INTO [zping.com] (Id,name)

VALUES (2,ZPING.COM2)

insert INTO [zping.com] (Id,name)

VALUES (3,ZPING.COM3)

insert INTO [zping.com] (Id,name)

VALUES (4,ZPING.COM4)

Back up the database, you can use the visualization of SQL Server, and the following is the generated code:

BACKUP DATABASE [ZP] to DISK = Nc:k.bak with Noformat, Noinit,

NAME = nzp-Full database backup, SKIP, Norewind, nounload, STATS = 10

Go

View data:

Note: This time we do a full backup

Some data may have been accidentally deleted. Let's simulate: (assuming that the 1,2 data is deleted)

Delete FROM [zping.com] where (id=1 or ID =2)

Note: Keep in mind the overall deletion time

Then found the deletion error, how to get the data back to AH. There are two methods:

1, search through log Explorer to recover data (my site has this tool)

2, another method restores the data by resuming the log (specifying Point-in-time restores).

Description: The first method can be operated online.

The second method must stop the database or restore another database (provided there must be a full backup and log backup)

Here we'll discuss the second method of specifying Point-in-time restores:

1, then the database transaction log backup (note, if you do not make a full database backup, can not do transaction log backup)

Then create a new database ZP (rename the previous database), restore the database

At this point, we see that there are two restored database backups, because I have backed up the ZP database two times, and the data files for the two backups are the same. Here we select the most recent time backup

The default settings in the database are as follows: Append to the backup set, so there will be two backups, as shown below:

Also, set the "Do not rollback" transaction in "Options".

Note: Restore the database through the transaction log, you must select the Do not ROLLBACK transaction

OK after: The following conditions occur:

It was found that the database was "being restored", and then the database transaction log was restored,

1, "General" in the selection of time, (just deleted time)

2, set the recovery status to ROLLBACK uncommitted transaction in options

After determining, query the database and find the data back.

Summarize:

1, this is a general large Web site data security method, because the database is relatively large (possibly hundreds of g) data, in order to ensure data security, most of the full backup + transaction log backup to ensure data security.

2, such as the Mirror in SQL Server 2005 is the use of this method of transaction log synchronization to ensure the synchronization of data.

3, if the recovered log data appears "LSN" too early and too late describes the discontinuity between transaction logs. Pay attention to the time and order of the backup.