Save your data and restore MSSQL data through logs.

Save your data and restore MSSQL data through logs.

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 zpcreate table [zping.com]( id int, name varchar(20))

2. 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:
[Code] backup database [zp] to disk = n' C: \ k. bak 'with noformat, NOINIT,
NAME = n'zp-full database backup ', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO [code]

View data:

Note:Then we make a complete backup.

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

Deletefrom [zping.com] where (id = 1 orid = 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 chooseRecent 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 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 "regular" (the time you just deleted)

2. Set the recovery status in "options" to "roll back uncommitted" transactions.

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 contain several hundred GB) data, it takes a long time to complete the backup and affects the performance of the database server, to ensure data security, most of them adopt full backup + transaction log backup to ensure data security. For example, a full backup is performed once a day or two days, and a log backup is performed within several hours. (Of course, you can write a job)

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

3. If the restored log data appears "LSN" too early or too late, it indicates the transaction log is not consecutive. Pay attention to the backup time and sequence.

The above is all the content of this article, hoping to help you learn.