Introduction to SQL Server log chain

Let's talk about these concepts first.

SQL Server only log chain, backup records (some people also called backup chain) I think it's more appropriate to call backup records.

Here are three things that say the same thing.

Backup set = Backup record = Backup chain

Backup set: For example, a collection of backups, such as a complete 1 of a database, the standby, daily standby 1, complete 2, daily standby 2, the backup of these databases is a collection of backup sets

But I prefer to call backup records.

Backup records actually refer to SELECT * from [msdb]. [dbo]. [Backupset]

Truncate log and log chain break is it the same thing?

Truncation log and log chain fracture not the same thing.

What is a log chain

In fact, we can understand the Bak file as a compressed package, full backup and differential backup will be the data and log into the compressed package,

and log Backups will only bring the log into the compressed package

Let's start with an experiment.

Test environment: SQLSERVER2012 Development version

Script

In order not to generate additional log, so the script does not have a SELECT INTO statement, would like to select into the temporary table and then sort the temporary table

However, because select into generates additional logs, only the Fn_dblog are sorted directly

Creating a Database

Use master   
Go   
-Creates database create DB   
logchaintest;   
Go   
--Change to Full recovery model   
ALTER DATABASE logchaintest SET RECOVERY;   
Go