SQL Server myth: A full backup is required to restart the log chain after the log backup chain has been destroyed

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:

Go

The result:

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

BACKUP LOG logchaintest to DISK = ' C:\SQLskills\LogChainTest_log2.bck ' with INIT;

Go

You will receive the following error message:

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.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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:

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:

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 fractured LSN, which is, of course, a deeper topic.

Also exposed a misunderstanding!