Understanding of transaction log backup and differential backup

First look at the concept:

Differential backup:Differential backup is for full backup: All files that change after the last full backup are backed up. (During differential backup, only the marked files and folders are backed up. It does not clear the mark, that is, it is not marked as a backup file after backup, in other words, it does not clear the archive attribute)

Transaction Log backup:The transaction log contains the changes made to the database after the last backup is created (either full backup or differential backup. Therefore, a complete database backup is required before backing up transaction logs. If you set the recovery mode to simple, you will not be able to use transaction log backup. SQL Server 2000 and SQL Server 2005: Create a transaction log backup. You must use a full recovery or large-capacity log recovery model.

Bytes ------------------------------------------------------------------------------------------------------------

The example is as follows:

There is a student table in the database with a test, which has three data items. The backup and data are as follows:

Student make a full backup of three data items in the table test_all.bak

Insert a data entry into the student table at, and perform a differential backup for four data entries. test_chayi1.bak also performs a log backup test_rizhi1.bak.

Insert another data entry into the student table at, and make a differential backup for five data entries. test_chayi2.bak also performs a log backup test_rizhi2.bak.

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15276/7BDB1D9EA0544DC5AE38EA92746BC903 "alt =" 7bdb1d9ea0544dc5ae38ea92746bc903 "/>

Recover data now:

How can I recover data?

The first approach (using differential backup to restore data ):

Step 1; first, I will delete the student table and restore the contents of the full backup test_all.bak. Student has three pieces of data.

Step 2: according to the definition of differential backup, to restore data at, we only need to restore test_chayi2.bak.

Let's start now:

First, restore the full backup. the following error is reported during the restoration process:

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15293/BE4F637C3B3547E5B2C7D3ED48327166 "style =" width: 497.2727272727272px; Height: 140px; "alt =" be4f637c3b3547e5b2c7d3ed48327166 "/>

The solution is as follows:

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15305/BF57C3F5DEC34343BA6CE6ACD28A34C2 "style =" height: 336.2893553223388px; width: 565px; "alt =" bf57c3f5dec34343ba6ce6acd28a34c2 "/>

Remember to select these two items. Otherwise, the differential backup cannot be restored. After the full backup is restored

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15310/F4DCA943899C49F581B8B328B38F52A8 "alt =" f4dca943899c49f581b8b328b38f52a8 "/>

Now the full backup has been restored. Let's recover the differential backup.

The following error is reported:

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15302/BAB757FDA0234B85B092155BCF004E18 "alt =" bab757fda0234b85b092155bcf004e18 "/>

The cause of this error is that the second item is not selected when the full backup is restored. "... do not roll back the submitted log .......".

Bytes ------------------------------------------------------------------------------------------------------------------------

Solution 2: Use transaction logs for recovery

According to the definition, you can know:

"The transaction log contains the changes made to the database after the last backup is created (either full backup or differential backup ".

Therefore, if the transaction log is used to restore the status.

First, we need to restore the complete backup and the last differential backup, that is, to restore the complete backup and the last differential backup. Restore the current transaction log backup.

Restore all data.

To better understand this concept, follow these steps:

Insert a data entry to the student table in the test database at, that is, a total of six data entries. Then, perform a log backup for test_rizhi3.bak.

By now, the preparations have been prepared, and our goal is to restore the status to through logs.

The recovery steps are as follows:

Step 1: restore the full backup. Note the following two options: "Overwrite existing database", "do not change Database", and do not roll back uncommitted items. Other Transaction logs can be restored.

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15357/1038EB31BB7640D2B9B088950F7356AE "style =" width: 590px; Height: 463.6248415716097px; "alt =" 1038eb31bb7640d2b9b088950f7356ae "/>

Step 2: recover the latest differential backup, that is, recover test_chayi2.bak. Select this option.

"Do not change the Database" and do not roll back uncommitted items. Other Transaction logs can be restored.

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15366/E55222E0CD6544949E30C2AD130FA57A "style =" line-Height: 1.5; Height: 562.02px; width: 696px; "alt =" e55222e0cd6544949e30c2ad130fa57a "/>

Step 3: Restore the current transaction log backup. Note. This option is not selected.

650) This. width = 650; "src =" http://note.youdao.com/yws/res/15370/E5EE1B3A94BC4F7FAD344793A83342BD "style =" width: 529px; Height: 532.8839941262848px; "alt =" e5ee1b3a94bc4f7fad344793a83342bd "/>

By now, all databases have been restored through transaction log backup.

Understanding of transaction log backup and differential backup