Myth #20: After breaking the log backup chain, a full backup is required to restart the log chain
Error
A transaction log backup backs up all transaction logs since the last transaction log backup (if there has never been a log backup, starting with the last full backup). There are several types of operations that break the continuity of the transaction log, meaning that SQL Server can no longer log backups unless a new log chain is restarted. The following actions can cause a log chain to break:
From the full recovery model or the bulk transaction log recovery model to the simple recovery model
Recovering from Database Mirroring
No_log or with TRUNCATE_ONLY was specified when backing up the log (fortunately this option was canceled in SQL Server 2008)
See more: Post BACKUP LOG with No_log-use, abuse, and undocumented trace flags to stop it
This is explained in the following example:
Copy Code code 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:
Copy Code code as follows:
168 pages have been processed for the database ' logchaintest ', file ' Logchaintest ' (located on file 1).
2 pages have been processed for the database ' logchaintest ', file ' Logchaintest_log ' (located on file 1).
BACKUP DATABASE successfully processed 170 pages, costing 0.224 seconds (5.916 MB/s).
3 pages have been processed for the database ' logchaintest ', file ' Logchaintest_log ' (located on file 1).
BACKUP LOG successfully processed 3 pages, costing 0.121 seconds (0.137 MB/s).
I first created a database and set it to the full recovery model, which is the starting point for the log chain and then to the simple recovery model and then the full recovery model.
I'll try the log backup again
Copy Code code as follows:
BACKUP LOG logchaintest to DISK = ' C:\SQLskills\LogChainTest_log2.bck ' with INIT;
Go
You will receive the following error message:
Copy Code code as follows:
Message 4214, Level 16, State 1, line 1th
Unable to perform backup LOG because there is currently no database backup.
Message 3013, Level 16, State 1, line 1th
BACKUP LOG is terminating abnormally.
SQL Server does not allow me to log backups because it has logged that I have broken the log chain and that all logs since the last log backup cannot be backed up by a log backup.
This myth is that you need a full backup at this time to restore the log chain, but in fact, I just need to do a differential backup (this differential backup spans over the log chain break), the code is as follows:
Copy Code code 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
The results obtained:
Copy Code code as follows:
64 pages have been processed for the database ' logchaintest ', file ' Logchaintest ' (located on file 1).
1 pages have been processed for the database ' logchaintest ', file ' Logchaintest_log ' (located on file 1).
The BACKUP DATABASE with differential successfully processed 65 pages and took 0.119 seconds (4.267 mb/sec).
1 pages have been processed for the database ' logchaintest ', file ' Logchaintest_log ' (located on file 1).
BACKUP LOG successfully processed 1 pages, costing 0.052 seconds (0.150 MB/s).
You have to say this is a much more cool way because you no longer need a full backup to continue the log backup.
If your backup strategy includes a backup of files or filegroups, you may even need a differential backup of a single file to continue the log backup. But the premise is that this backup spans more than the length of the fault LSN, and this is a deeper topic, of course.
Also exposed a misunderstanding!
