SQL Server misunderstanding on the last 30 days: After the log backup chain is damaged on the seventh day, a complete backup is required to restart the log chain.

Misunderstanding #20: After the log backup chain is damaged, a complete backup is required to restart the log chain.
Error

Transaction Log backup backs up all transaction logs since the last transaction log backup (if there is no log backup, it starts from the last full backup ). There are several types of operations that interrupt the continuity of transaction logs. That is to say, SQL Server cannot back up logs unless a new log chain is restarted. The following operations may cause the log chain to break:

Switching from full recovery mode or large transaction log recovery mode to simple recovery mode
Restore from database image
NO_LOG or WITH TRUNCATE_ONLY is specified during log backup (this option is canceled in SQL Server 2008)

For more information, see post backup log with NO_LOG-use, abuse, and unregister ented trace flags to stop it.

The following example is used to describe this problem:

Copy codeThe Code is as follows: create database LogChainTest;
GO
Alter database LogChainTest set recovery full;
GO
Backup database LogChainTest to disk = 'C: \ SQLskills \ LogChainTest. bck 'with init;
GO
Backup log LogChainTest to disk = 'C: \ SQLskills \ LogChainTest_log1.bck 'with init;
GO
Alter database LogChainTest set recovery simple;
GO
Alter database LogChainTest set recovery full;
GO

The result is:Copy codeThe Code is as follows: the database 'logchaintest' and the file 'logchaintest' (located on file 1) processes page 168.
The database 'logchaintest' and the file 'logchaintest _ log' (located on file 1) are processed on two pages.
The backup database successfully processes 170 pages and takes 0.224 seconds (5.916 MB/second ).
The database 'logchaintest' and the file 'logchaintest _ log' (located on file 1) are processed on three pages.
The backup log successfully processes three pages and takes 0.121 seconds (0.137 MB/second ).

First, I created a database and set it to the full recovery mode. This is the starting point of the log chain, and then switched to the simple recovery mode, and then to the full recovery mode.
Next, I will try to back up logs.Copy codeThe Code is as follows: backup log LogChainTest to disk = 'C: \ SQLskills \ LogChainTest_log2.bck 'with init;
GO

The following error message is displayed:Copy codeThe Code is as follows: Message 4214, level 16, status 1, 1st lines
Backup log cannot be executed because there is no Database BACKUP currently.
Message 3013, level 16, state 1, 1st rows
The backup log is being terminated abnormally.

SQL Server has recorded my operations that damage the log chain and cannot back up all logs since the last log backup. Therefore, SQL Server does not allow me to back up logs.
This misunderstanding means that a full backup is required to restore the log chain, but in fact, I only need to make a differential backup (the span of the differential backup exceeds the gap between the log chain break). The Code is as follows:Copy codeThe Code is as follows: backup database LogChainTest to disk = 'd: \ Test_bak \ LogChainTest_log1.bck 'with init, DIFFERENTIAL;
GO
Backup log LogChainTest to disk = 'd: \ Test_bak \ LogChainTest_log1.bck 'with init;
GO

Result:Copy codeThe Code is as follows: the database 'logchaintest' and the file 'logchaintest' (located on file 1) processes 64 pages.
The database 'logchaintest' and the file 'logchaintest _ log' (located on file 1) are processed on one page.
Backup database with differential successfully processes 65 pages and takes 0.119 seconds (4.267 MB/second ).
The database 'logchaintest' and the file 'logchaintest _ log' (located on file 1) are processed on one page.
The backup log successfully processes one page and takes 0.052 seconds (0.150 MB/second ).

I have to say that this method is Cool, because you no longer need a full backup to continue log backup.
If your backup policy contains a file or a file group backup, you can continue to perform log backup only by performing differential backup on a single file. But the premise is that the backup span exceeds the length of the broken LSN. Of course this is a deeper topic.
Another misunderstanding has been exposed!