Transaction Log backup and recovery 3

ArticleDirectory

• 14.3 generate backup Sets

14.3 generate backup Sets

Through the previous study, we have learned that the SQL Server 2005 database provides the function to restore the database status to the fault occurrence point. However, the smooth execution of these functions requires some prerequisites. For example, the online log cannot be damaged. Otherwise, the transaction from the last log backup time to the fault point will be lost.

Many DBAs do not understand the mysteries of this process. They often take it for granted that using existing backup logs can restore the database to a fault point, and forget the mysteries of restoring the database by actually performing a log backup.

Next, we will use a specific instance to restore the database to the fault point.

14.3.1 case design

The Design and completion of the case are as follows.

1. Case steps

(1) Create a database db_test. When the database is working in a fully recovered model, create the t_clusterindextest table and enter 1001 data records into the table. Query the log intervals of the Database Log File records.

(2) perform a full database backup. Query the log range of the database after the backup and the log range in the backup set.

(3) Delete 99 data records, generate transaction logs, and query the log range of the database.

(4) Complete transaction log backup for 1st times. You can follow the default settings, that is, it is not the end log backup. Query the database log interval after backup and the log interval of the Backup set.

(5) delete 101 data records, generate transaction logs, and query the log range of the database.

(6) You can back up transaction logs 2nd times by default, that is, not the end log backup. Query the database log interval after backup and the log interval of the Backup set.

(7) delete one record in the table and simulate a fault after deletion.

(8) back up the tail log and try to restore it.

The steps of the case can be expressed in Figure 14-8.

Figure 14-8 Procedure

2. Verification ideas

In the final backup set formed during the backup process, we can verify it in this way.

(1) Use full database backup + Log Backup 1 to restore the database to the state of deleting 99 records.

(2) You can use full database backup + Log Backup 1 + Log backup 2 to restore the status of deleting 200 records from the database, however, the database cannot be restored to the state of deleting 201 records.

(3) due to tail log backup, the database is restored to the state of deleting 201 records by using full database backup + Log Backup 1 + Log backup 2 + tail log backup.

3. Conclusion

The preceding experiment shows the importance of tail log backup in restoring the database to a fault point. Readers can deeply understand the root cause that online logs cannot fail.

14.3.2 generate backup set

Next we will introduce how to form a backup set.

1. Generate a database

Create a new db_test database in the same way as in the previous chapter. Create the t_clusterindextest table and generate 1001 pieces of data.

Execute the DBCC log command to query the database logs 14-9.

-Current lsn: 0000001d: 0000001a: 1st of 0001 log records.

-Current lsn: 0000001d: 00000137: 00a2 of the last log record.

Figure 14-9 logs generated after the database

2. Generate full database backup

(1) generate a full database backup according to the interface shown in figure 14-10.

Figure 14-10 create a full database backup

(2) run the DBCC log command to query the database logs, as shown in figure 14-11.

Figure 14-11 logs generated after full database backup

-Current lsn: 10000001d: 1st: 00b3 of 00000166 log records.

-Current lsn: 0000001d: 000001b5: 0003 of the last log record.

(3) run the restore headeronly command to query logs in the backup set, as shown in 14-12.

Figure 14-12 generate backup set logs after full database backup

Ø-firstlsn: 29000000035800179.

-Lastlsn: 29000000043400001.

3. 1st log backups

(1) execute the followingCodeDelete 99 records.

Delete from db_test.dbo.t_clusterindextest

Where t_t_id <= 99

CD code: \ code \ 1402. SQL.

(2) run the DBCC log command to query database logs, as shown in figure 14-13.

Figure 14-13 delete 99 database logs

-Current lsn: 10000001d: 1st: 00b3 of 00000166 log records.

-Current lsn: 0000001d: 000001ba: 0067 of the last log record.

(3) set backup database logs by default as shown in figure 14-14.

You can also execute the following code to complete the same function. Note that this is not the end log backup, but the truncation.

Backup log [db_test] to disk = n'c: \ test2.bak'

With noformat,

Noinit,

Name = n' dB _ test-transaction log backup ',

Skip,

Norewind,

Nounload,

Stats = 10

Go

CD code: \ code \ 1403. SQL.

(4) run the DBCC log command to query the backed up database logs 14-15.

-Current lsn: 10000001d: 1st: 00b3 of 00000166 log records.

-Current lsn: 0000001d: 000001ba: 0067 of the last log record.

(5) run the restore headeronly command to query the logs in the backup set, as shown in log 14-16.

Figure 14-14 back up transaction logs

Figure 14-15 database logs backed up after 1st log backups

Figure 14-16 backup set logs generated after 1st log backups

4. 2nd log backups

(1) execute the following code to delete 101 records.

Delete from db_test.dbo.t_clusterindextest

Where t_t_id> 99 and t_t_id <= 200

CD code: \ code \ 1404. SQL.

(2) run the DBCC log command to query the deleted Database Log 14-17.

Figure 14-17 Delete database logs with 101 records

-Current lsn: 10000001d: 1st: 00b3 of 00000166 log records.

-Current lsn: 0000001e: 00000010: 0008 of the last log record.

(3) 2nd logs are backed up, and no tail logs are backed up.

(4) run the DBCC log command to query the logs of the backed up database, as shown in 14-18.

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

-Current lsn: 0000001e: 00000027: 0001 of the last log record.

Figure 14-18 database logs backed up after 2nd transaction logs

(5) run the restore headeronly command to query the log range 14-19 in the backup set.

Figure 14-19 backup set logs after 2nd log backups

5. Simulate fault occurrence

(1) execute the following code to delete one record.

Delete from db_test.dbo.t_clusterindextest

Where t_t_id = 555.

CD code: \ code \ 1405. SQL.

(2) run the DBCC log command to query database logs, as shown in figure 14-20.

Figure 14-20 logs generated when a fault occurs

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

-Current lsn: 0000001e: 0000004c: 0005 of the last log record.

6. Tail log backup

(1) select backup log, and on the tab shown in 14-21, select back up the last log.

(2) You can also execute 1401. SQL to complete the same process, as shown in 14-22.

7. database logs

After all the operations are completed, run the DBCC log command to query the database logs 14-23.

Figure 14-21 back up the end log

Figure 14-22 execution of tail log backup

Figure 14-23 database logs after the backup is completed

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

-Current lsn: 0000001e: 00000050: 0001 of the last log record.

8. Final backup set log

The log for querying the final backup set is 14-24.

Figure 14-24 last backup set log