After a log backup chain is interrupted, does it need to be restarted from a full backup?

In general, we understand that after the logbackup chain is broken, a complete backup is required before a new log backup starts. In fact, it is not necessarily necessary to complete the backup, and differential backup is also possible.

 

 

First, let's test: Create a database for full backup and log backup, and then change the recovery mode to simple recovery mode.

 

 

CREATEDATABASE LogChainTest;
GO
Alter database LogChainTest set recovery full;
GO
Backup database LogChainTest to disk = 'd: \ MSSQL \ LogChainTest. bck 'with init;
GO
Backup log LogChainTest to disk = 'd: \ MSSQL \ LogChainTest_log1.bck 'with init;
GO
Alter database LogChainTest set recovery simple;
GO
Alter database LogChainTest set recovery full;
GO

 

 

After a new Log backup is started, the following error occurs:

 

 

BACKUP LOGLogChainTest to disk = 'd: \ MSSQL \ LogChainTest_log2.bck 'with init;
GO

Msg 4214, Level 16, State 1, Line 1
Backup log cannot be stored med because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
Backup log is terminating abnormally.

 

 

As we all know, Database Log backup contains transactions that have occurred since the last log backup. When we change the database mode to simple, SQL Server will think that this log backup will not contain all the log transactions, so the log backup cannot be completed.

 

 

Now let's make a differential backup and then start the log backup. This will be successful:

 

 

BACKUPDATABASE LogChainTest to disk = 'd: \ MSSQL \ LogChainTest_Diff1.bck 'with init, DIFFERENTIAL;
GO
Backup log LogChainTest to disk = 'd: \ MSSQL \ LogChainTest_log2.bck 'with init;
GO

 

 

After the log backup is complete, I did a recovery test, and there was no problem at all.

 

 

This function is very useful for large databases. If the Logbackup chain is broken, you can directly select differential backup instead of performing a full backup, which saves a lot of time and resources.

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1032127