How to restore SQL server data through Logs

During this time, I read about restoring data in SQLserver through Logs and time points. I also read some online examples to see how to restore data through logs. Prerequisites: the fault recovery mode of the database is changed to non-simple mode, and the options of automatic shutdown and automatic contraction are removed. If the option is simple mode, the operation data similar to the following statements will not be recorded in the log.

During this time, I read about how to restore data through Logs and time points in SQL server. I also read some online examples to see how to restore data through logs. Prerequisites: the fault recovery mode of the database is changed to non-simple mode, and the options of automatic shutdown and automatic contraction are removed. If the option is simple mode, the operation data similar to the following statements will not be recorded in the log.

During this time, I read about how to restore data through Logs and time points in SQL server. I also read some online examples to see how to restore data through logs.

Prerequisites:

Change the database fault recovery mode to non-simple mode. Remove the options of automatic shutdown and automatic contraction.

In the simple mode, the operation data similar to the following statement is not recorded in the log: select * into t from [Table name]

To ensure data integrity, you must change the database recovery mode to "complete"

Test environment:

1. Create a database and a test table

Create database zp

Create table [zping. com] (

Id int,

Name varchar (20)

)

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)

To back up a database, you can use SQL server visual operations. The generated code is as follows:

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: now we have a complete backup.

Some data may be accidentally deleted. Let's simulate: (assume that 1 or 2 data is deleted)

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

Note: Remember the general deletion time.

The deletion error is found. How can I retrieve the data. There are two methods:

1. Restore data through Log Explorer search (this tool is available on my website)

2. Another method is to restore data by restoring logs (recovery at a specified time point.

Note: The first method can be operated online.

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

Here we will discuss how to restore at the specified time point in Method 2:

1. Back up the transaction logs of the database at this time (Note: if you do not have a full backup of the database, you cannot back up the transaction logs)

Create a new database zp (rename the previous database) and restore the database.

At this time, we can see that there are two restored Database backups, because I have backed up the zp database twice, and the two backups have the same data files. Here we select the latest backup

The default settings in the database are as follows: it is appended to the backup set, so there will be two backups, such:

At the same time, set "do not roll back" transaction in "option,

Note: to restore a database using transaction logs, You must select "do not roll back ".

After confirmation: the following situation occurs:

At this time, we found that the database has been "restoring". At this time, we restored the database transaction log,

1. Select a time in "General" (the time when the deletion was just completed)

2. In "options", set the recovery status to "rollback not committed ".

After confirming, query the database and find that the data is back.

Summary:

1. This is a general approach to data security for large websites. Because the database is large (may have several hundred GB of data), to ensure data security, most of them use full backup + transaction log backup to ensure data security.

2. For example, the image in SQL server 2005 adopts the transaction log synchronization method to ensure data synchronization.

3. If "LSN" is too early and too late "appears in the recovered log data, the transaction logs are not consecutive. Pay attention to the backup time and sequence.