Transaction Log backup and recovery 1

Document directory

• 14.1 principles of transaction log backup and recovery
• 14.1.1 principles of transaction log backup and recovery
• 14.1.2 mysteries of transaction log backup continuity
• 14.1.3 restoration to the mysteries of instant points
• 14.1.4 restoration to fault point
• 14.1.5 tail log backup

Key points of this Chapter

Principles of producer transaction log backup and recovery

Tail log backup

Generating a backup set

Restore the database to the fault point

Issues in recovery from backups

It is difficult for a DBA who does not understand transaction logs to master the essence of the database. Transaction logs faithfully record the activities of the database. Therefore, the activities based on these records can restore the database status to a specific instant point or to a fault point at will.

However, not every DBA can perform these operations correctly. Where are the mysteries?

This chapter provides an in-depth study of transaction log backup and recovery operations.

14.1 principles of transaction log backup and recovery

Next we will first learn the principles of transaction log backup and recovery.

14.1.1 principles of transaction log backup and recovery

Transaction Log backup can only be used with the full recovery model and large-capacity log record recovery model. In a simple model, transaction logs may be damaged, so the transaction log backup may be discontinuous, and the non-sequential transaction log backup is meaningless, because log-based recovery requires that logs be continuous.

You can use the transaction log backup to restore the database to a specific instant point (such as the point before excess data is input) or to a fault point. When the transaction log backup is restored, SQL Server 2005 rewrites all the changes recorded in the transaction log. When SQL Server 2005 reaches the end of the transaction log, it has re-created the database status that is exactly the same as the moment when the backup operation is started. If the database has been recovered, SQL Server 2005 rolls back the backup operation to start all unfinished transactions.

Generally, transaction log backup uses less resources than database backup. Therefore, transaction log backup can be created more frequently than database backup. Regular backup reduces the risk of data loss.

Figure 14-1 shows a policy based on one full backup + N consecutive transaction log backups under the full recovery model. If the intermediate log backup 02 is deleted or damaged, the database can only be recovered to the instant point of log backup 01.

Figure 14-1 principles of transaction log backup and recovery

If log backup 01, 02, and 03 are complete, recover the full database backup first, and then recover the log backup 01, 02, and 03 in sequence. If you want to recover to the fault point, you need to check whether the current log of the database is complete. If it is complete, you can back up the current log and restore the log backup in sequence 04.

Transaction Log-based backup can also be restored to the time point in the middle of a log backup. For example, we can restore the database at a certain point in the middle of the full backup and log backup 01 After the database is completely backed up. This is the point in time recovery. The time point here must be valid (view the log backup time), but cannot exceed the time series of log backup, otherwise the system will not execute. For example, currently only log backup 01 is available, and its time is. If we specify to restore to, this time point is invalid.

14.1.2 mysteries of transaction log backup continuity

Continuous transaction log backup is the basic requirement for backing up and recovering transaction logs. So what kind of transaction log backup is continuous?

LSN (log serial number) is the basic method used to determine whether transaction log backup is continuous.

1. Continuous transaction log backup

After we create a backup through the backup operation, we can execute the restore headeronly statement to check the transaction log backup in the backup set and determine whether it is continuous.

Restore headeronly from disk = 'C: "test2.bak'

The result is 14-2.

We can conclude that the transaction log backup sequence is continuous! Why?

Because the lsn of the transaction log backup is connected at the beginning and end, the firstlsn of the last log backup is equal to the lastlsn of the previous log backup.

-Log backup (Number 2): firstlsn: 29000000035800179, lastlsn: 29000000047000001.

-Log backup (Number 3): firstlsn: 29000000047000001, lastlsn: 30000000001900001.

-Log backup (number 4): firstlsn: 30000000001900001, lastlsn: 30000000008100001.

Figure 14-2 transaction log backup sequence of the Instance

According to the three log backup sequences, the starting point of the transaction log recorded by the log is the firstlsn: 1st of the 29000000035800179 log backups. The end point is the lastlsn: 30000000008100001 of the last log backup.

CD video: "video" 1402.exe( continuous transaction log backup ).

2. Non-sequential transaction log backup

Discontinuous log backup refers to the case where the start and end cannot be continued in the generated log backup sequence. This situation mainly happens to beginners or DBAs who have just started to switch the database recovery model, for example, switching from a simple recovery model to a full recovery model, you can switch from a fully recovered model to a large-capacity logging model!

Assume that the following situations occur in your log backup. Therefore, the beginning and end of such a log sequence lsn cannot be connected and cannot be connected for recovery!

-Log backup (Number 2): firstlsn: 29000000035800179, lastlsn: 29000000047000001.

-Log backup (Number 3): firstlsn: 30000000001900001, lastlsn: 30000000008100001.

Tip: DBAs must do everything they can to ensure the security of the current log and log backup sequence, and ensure the integrity of the log sequence. The method to determine whether the log sequence is complete is to execute the restore headeronly statement.

14.1.3 restoration to the mysteries of instant points

It is precisely because of the continuous and complete transaction log backup sequence, combined with a complete database backup, we can restore the database status to any instant point in the middle of the log sequence.

However, this is a prerequisite.

1. correct and complete database backup

First, you must have a correct and complete database backup. Why do you need to emphasize the word "correct" here? If you have a correct understanding of the continuity concept of transaction logs, the word "correct" indicates that the full database backup must be completed between the time points of the 1st log backup sequence.

This book includes a Bak backup file, including one full database backup and three continuous transaction log backup. We can see that the number 1 is full database backup, the other three are transaction log backups. As shown in figure 14-3.

Figure 14-3 correct full database backup

The log range for full database backup is: 29000000035800179 ~ 29000000043400001.

The backup interval for the 1st transaction logs is: 29000000035800179 ~ 29000000047000001.

Therefore, the complete database backup here is correct, because the restoration of the full database backup will stay in a real-time point in the middle of transaction log Backup 1. Then, you can continuously back up transaction logs for restoration.

What is an incorrect full database backup?

The real-time point after the complete database backup is out of the log backup sequence. As shown in figure 14-4.

Figure 14-4 Principles of transaction log backup and recovery

Tip: You can never expect to stay out of log backup. The full database backup and log backup at the instant point can be used together for restoration, this is because the log sequence of the full database backup and log backup is interrupted.

We can understand this process as a train working mechanism. The front engine (full database backup) and the carriage (log backup sequence) are connected at the beginning and end, so we can go from the beginning to the end. If there is a break between the front and the carriage (incorrect full database backup), we can only drive away (restore the database to the real-time point after full database backup )! Similarly, if there is a break between trains (the transaction log backup sequence is broken), we can only go to the connected part (recovering the continuous log backup sequence), and the rest is meaningless!

2. correct instant points

The meaning of this sentence is that never expect to restore the database status beyond the lsn range recorded in the log sequence!

This is easy to understand, because the lsn does not record database activities, and the database recovery mechanism is untrustworthy, how can we restore it?

14.1.4 restoration to fault point

Whether we read SQL Server 2005 books online or relevant materials, we will tell us that SQL Server 2005 has the ability to restore the database to a fault point!

However, unfortunately, there is not a book that tells us how the author recovers the database to the point of failure!

First, we understand the mysteries of restoring to the fault point in principle, as shown in Figure 14-5.

Figure 14-5 restoration to fault point

We can see that to restore the database to the fault point, three conditions must be met.

1. correct and complete database backup

This is easy to understand, and DBA generally does.

2. Continuous transaction log backup Sequence

Unless the storage device fails, the problem is also well solved.

3. Correctly back up the logs between the last log backup and the fault point

This is hardly mentioned in the current SQL Server 2005 book! A dba with a little practical experience (here refers to a DBA who is actually engaged in large-scale database management) will surely realize the seriousness of this problem!

If we operate according to other books on the market, when a fault occurs, we will never be able to restore the database to the fault point, because we can only restore the database to the time when the last log backup is completed, what about the logs between the time when the last log backup is completed and the time when the fault point is located?

Waiting for the DBA will be the miserable fate of being fired by the boss!

Tip: to restore the database to a fault point, you must have a deep understanding of the principles of SQL Server 2005 tail log backup.

Obviously, we must copy this special log (the last log backup time ~ Fault point occurrence time) back up, so that all log backup sequences from the full database backup time to the fault point time are complete! As shown in figure 14-6.

Figure 14-6 tail log backup

14.1.5 tail log backup

Therefore, to restore the fault point, you must back up the end log. Next, let's take a look at the topics related to tail logs.

1. Tail log storage

First, where is the tail log stored?

Obviously, the answer is in the current log file. The contents saved in the Current Log File Include the last successful log backup to all transactions at the current fault point.

Therefore, once the log file of the database between the last log file backup and the fault point unfortunately suffers a media fault, such as the hard disk storing the log file is damaged, in this case, god cannot save the fate of a DBA!

We can see the importance of log files to databases and DBAs!

Therefore, if the end log backup cannot be completed, the database can only be restored to the point when the last transaction log backup is created. Changes made to the database after the last transaction log Backup will be lost and must be redone manually.

2. Differences from normal log backup

Similar to normal log backup, tail log backup will capture all transaction logs that have not yet been backed up. However, tail log backup is different from normal log backup in the following aspects.

-If the database is damaged or is offline, you can back up the tail log. Only when the log file is not damaged and the database does not contain any large-capacity Log changes, the tail log backup will succeed. If the database contains the large-capacity Log changes to be backed up during the record interval, the tail log backup is successful only when all data files exist and are not damaged.

-You can use the copy_only option to create tail log backup independently of regular log backup. Copying a backup only does not affect the backup log chain. Transaction logs will not be truncated by the end log backup, and the captured logs will be included in subsequent normal log backup. In this way, tail log backup can be performed without affecting the normal log backup process, for example, to prepare for online restoration.

-If the database is damaged, the tail log may contain incomplete metadata, because some metadata that can be used for log backup may be unavailable in the tail log backup. Log Backup Using continue_after _ error may contain incomplete metadata, because this option will notify log backup without considering the database status.