Transaction Log backup and recovery 5

ArticleDirectory

• 14.5 restoration to fault point using the Bak File

14.5 restoration to fault point using the Bak File

If the database is damaged, we can only use the backup set file (usually with the Bak extension) to restore the database. If the backup set contains the last log backup, we can also restore the database to the fault point.

We have introduced how to use the restore headeronly command to view the header information of the Backup set file. The information here is consistent with the information saved in the MSDB system database.

The difference is that when deleting a database, we can choose whether to delete the backup information in the MSDB System database at the same time, and the backup information of the Backup set file is stored in its header, will not be deleted with the deletion of the backup information of the MSDB system database.

Problems found in 14.5.1

In management studio, Select Restore database, Select Restore from device, and set the device to a Bak file. The General tab shown in 14-27 is displayed.

Figure 14-27 General Tab

However, we are surprised that, even though the backup set has three log backups (two log backups + one tail log backup ), in addition, the lsn of the three log backups is followed by each other. However, in Figure 14-26, we can only find two log backup sequences, but the backup sequence of the last log is invisible, after repeated experiments, this problem persists.

Because tail log backup cannot be applied, the database cannot be recovered to the fault point! Is tail log backup unusable?

14.5.2 Solution

After several repeated experiments, we found that this problem cannot be solved in graphical operations. Although the backup sequence of the last log is connected to the first and end of the previous log backup sequence, it cannot be selected in the graphical interface.

The author looks at the Restore database and restore log statements. Finally, the problem was solved successfully.

1. Successful instances

The following is an example of a statement that successfully restores the end log.

Restore database [db_test] from disk = n' c: \ test2.bak'

With file = 1,

Norecovery,

Nounload,

Replace,

Stats = 10

Go

Restore log [db_test] from disk = n' c: \ test2.bak'

With file = 2,

Norecovery,

Nounload,

Stats = 10

Go

Restore log [db_test] from disk = n' c: \ test2.bak'

With file = 3,

Norecovery,

Nounload,

Stats = 10

Go

Restore log [db_test] from disk = n' c: \ test2.bak'

With file = 4,

Nounload,

Stats = 10

Go

CDCode: \ Code \ 1407. SQL.

2. Solution

The following statement restores the end log.

Restore log [db_test] from disk = n' c: \ test2.bak'

With file = 4,

Nounload,

Stats = 10

It can be seen that the preceding statement for restoring the tail log is different from the statement for restoring the sequence of logs.

Restore log [db_test] from disk = n' c: \ test. Bak'

With file = 3,

Norecovery,

Nounload,

Stats = 10

The most essential difference is that the tail log restoration parameter norecovery is missing.

3. Mysteries of the norecovery Parameter

So why can the norecovery parameter restore the end log?

The recovery parameter indicates that the restore operation rolls back any uncommitted transactions. The database can be used at any time after the process is recovered. If neither norecovery nor recovery are specified, the default value is recovery.

The norecovery parameter indicates that the restore operation does not roll back any uncommitted transactions. If you must apply another transaction log later, specify the norecovery or standby option. When you use the norecovery option to perform an offline Restoration Operation, the database cannot be used.

4. Usage

When restoring a database backup and one or more transaction logs, or multiple restore statements (for example, restoring a complete database backup and then restoring a complete differential backup, restore must use the with norecovery option for all statements, except for the last restore statement.

14.5.3 verify that the fault point is restored

(1) Run 1407. SQL. the execution result is 14-28.

Figure 14-28 restore execution

(2) run the DBCC log statement to query the logs of the recovered database, as shown in figure 14-29.

-Current lsn: 0000001e: 1st: 00000013 of 0001 log records.

-Current lsn: 0000001e: 00000064: 000a of the last log record.

Figure 14-29 recovered database logs

In Figure 14-23, we know that the cureent lsn of the last log record of the Database Log after the last log backup is: 0000001e: 00000050: 0001.

Since the lsn (0000001e: 00000144: 000a)> lsn (0000001e: 00000050: 0001) of the fault point of the recovered log, we come to the conclusion that, our recovery operations indeed restored the database to a fault point. The redundant lsn is a log record generated by backup and recovery operations.

(3) theoretically, we have verified that, according to our experiment data, the last log record at the fault point should appear in the recovered log.

Next, we run the DBCC log statement to query the log record, and found that the log record exists in the log record, as shown in figure 14-30.

Figure 14-30 recovered database logs

(4) We can also query the data of specific tables in the database to determine whether the fault point has been restored.

Run the following statement. The query result shows 799 pieces of data, which is the data in the database when the fault point is simulated.

Select count (*) from db_test.dbo.t_clusterindextest