BACKUP LOG Cannot Be Performed Because There Is No Current Database Backup (Error 4214)

Your log backup job has started failing on one database with Msg 4214. Nothing is broken. The database is in the FULL recovery model, but SQL Server has no full backup to start a log chain from, usually because the database is new or was switched from SIMPLE to FULL. Take one full backup and the next log backup works.

It is followed by Msg 3013, BACKUP LOG is terminating abnormally, which adds nothing.

Check Whether the Log Chain Has Started

Run this on the instance. It lists every database in FULL or BULK_LOGGED, and the ones with a NULL in the last column are the ones that will fail with 4214:

SELECT d.name,
       d.recovery_model_desc,
       rs.last_log_backup_lsn   -- NULL = no log chain yet, BACKUP LOG fails with 4214
FROM sys.databases AS d
JOIN sys.database_recovery_status AS rs ON rs.database_id = d.database_id
WHERE d.recovery_model_desc <> 'SIMPLE'
ORDER BY rs.last_log_backup_lsn, d.name;

Despite its name, last_log_backup_lsn is not about the last log backup that ran. It is the point the next log backup would start from, and a full backup is what sets it. The column is described on sys.database_recovery_status on MS Docs.

Take a Full Backup, Then the Log Backup

-- Change the database name and paths to yours
BACKUP DATABASE YourDatabase TO DISK = 'D:\Backup\YourDatabase_full.bak' WITH CHECKSUM, INIT;
BACKUP LOG YourDatabase TO DISK = 'D:\Backup\YourDatabase_log.trn' WITH CHECKSUM, INIT;

After that, last_log_backup_lsn has a value and the log backup job runs normally. On a large database you can let the next scheduled full backup do this instead, as long as you accept no log backups, and no point-in-time restore, until it finishes.

What Leaves a Database Without a Log Chain

• A new database. It takes its recovery model from model, which is FULL by default, and the log backup job picks it up before the first full backup has run.
• A switch from SIMPLE to FULL. Switching to SIMPLE breaks the log chain, and switching back does not restore it. A database with a working chain a minute earlier still failed.
• A COPY_ONLY full backup. It does not start a log chain. A new database with only a COPY_ONLY full backup still gave 4214 on the next log backup.

The switch is the one that comes back, because something keeps doing it: a maintenance job that shrinks the log, a deployment script, or a person clearing a full transaction log. Here the database had a full and a log backup, was switched to SIMPLE and back, and the next log backup failed:

After a switch from SIMPLE, a differential backup is enough to restart the chain, because a full backup already exists. That worked here, and MS Docs on changing the recovery model recommends it.

Frequently Asked Questions