Why Tail-Log Backups Matter for SQL Server Recovery and Migration

In previous posts, we’ve covered the more routine types of backups available within SQL Server — full, differential, and transaction log backups. While you may not use them as often, you should also be aware of tail-log backups when managing SQL Server. Tail-log backups can help in two scenarios.

1. Recovering after a database outage
2. Database migrations

What is a tail-log backup?

Tail-log backups capture transaction log records that haven’t been backed up yet and set the database into a “restoring” state. Think of it as the last transaction log backup taken right before a database is restored, moved, or damaged. Its primary purpose is to capture the “tail” end of the log (any transactions that occurred since the last regular log backup to prevent data loss).

When are tail-log backups important?

Scenario 1: Preventing data loss after a failure

Imagine that you schedule transaction log backups to occur every 15 minutes. At 10:05 AM, the server hosting your database experiences a critical failure, taking the database offline. Your last log backup was at 10:00 AM. This means that transactions that have occurred between 10:00 AM and 10:05 AM would be lost without a tail-log backup.

The tail-log backup bridges this gap, capturing those final crucial log records and allowing you to restore the database to the exact point of failure. Depending on the damage, you may need to add additional options for this backup to succeed. See here for those options.

Scenario 2: Supporting a smooth database migration

If you’re migrating a database to a new instance, tail-log backups can be beneficial as well. Let’s say you have a migration coming up from SQL Server 2016 to a new SQL Server 2022 instance. You’ve pre-staged the database on the new instance by restoring the latest Full and have been applying transaction log backups periodically without recovery throughout the week leading up to migration day. On migration day, you need to ensure the following happens:

1. All transactions are captured up to and right after the maintenance window begins.
2. Ensure the database on the old instance can no longer accept new connections. You don’t want new transactions landing here by accident.
3. Restore the final transaction log backup on the new instance and recover the database.

A tail-log backup can help in this scenario as well. You see, once you perform a tail-log backup, the database you performed it on is placed into the “restoring” state, which doesn’t allow connections. This ensures no future transactions can be written. Just in case a connection string wasn’t updated somewhere during the migration (I’d personally rather the connection fail vs. being able to connect to the database that is no longer in use). Once the migration is complete, including testing and validation, the old database can be dropped.

Let’s take a look at the requirements needed before you can utilize tail-log backups.

Requirements for tail-log backups

To perform a tail-log backup, the following conditions must be met:

Recovery model

The database must be in the FULL or BULK_LOGGED recovery model. Tail-log backups are not possible (or needed) in the SIMPLE recovery model, as the transaction log is automatically truncated.

Prior full backup

At least one full database backup must have been taken previously.

Log file accessibility

The transaction log file (.ldf) must be accessible and largely intact, even if the data files are damaged or the database is offline.

You can determine if your database meets the first two requirements by using a query similar to the one below.

USE MSDB
SELECT distinct
	backupSet.[Database_Name],  
	s.recovery_model_desc AS RecoveryModel,
	backupmediafamily.logical_device_name AS LogicalDeviceName, 
	backupmediafamily.physical_device_name AS PhysicalDeviceName, 
	backupset.expiration_date AS ExpirationDate, 
	backupset.name AS Name, 
	backupset.[description] AS [Description], 
	backupset.user_name AS UserName, 
	backupset.backup_start_date AS StartDate, 
	backupset.backup_finish_date AS EndDate, 
	DATEDIFF(mi, backupset.backup_start_date, backupset.backup_finish_date) AS DurationInMinutes,
	CAST(CASE backupset.type 
	WHEN 'D' THEN 'Database' 
	WHEN 'L' THEN 'Log' 
	WHEN 'I' THEN 'Differential' 
	WHEN 'F' THEN 'File' 
	WHEN 'G' THEN 'Diff File' 
	WHEN 'P' THEN 'Partial' 
	WHEN 'Q' THEN 'Diff Partial' 
	END AS NVARCHAR(128)) AS BackupType, 
	--backupset.compressed_backup_size / 1048576 , 
--	backupset.backup_size / 1048576,
	ISNULL(backupset.compressed_backup_size, backupset.backup_size) / 1048576 as SIZE,
	is_snapshot,
	is_copy_only,
	GetDate() AS DateChecked --, *
FROM msdb.dbo.backupmediafamily AS backupmediafamily
INNER JOIN msdb.dbo.backupset AS backupset ON backupmediafamily.media_set_id = backupset.media_set_id
INNER JOIN master.sys.databases as s ON backupset.database_name = s.name
WHERE     (CONVERT(datetime, backupset.backup_start_date, 102) >= GETDATE() - 1)
AND backupset.server_name = @@servername  --Filters out databases that were restored from other instances.
AND backupSet.[Database_Name] = 'YourDatabaseName'
AND backupset.type = 'D'
--AND backupSet.Type = 'D'
ORDER BY StartDate --DurationInMinutes DESC

How to perform a tail-log backup (high-level)

The command is a variation of the standard BACKUP LOG statement. The key difference often lies in the options used, particularly WITH NORECOVERY or WITH NO_TRUNCATE.

Scenario 1: Database damaged/not starting (log file intact)

In this scenario, you may have lost a drive containing your data files (.mdf). You’re lucky because you’ve been following the old adage of keeping your data files and log files on separate disks so the log file is available.

If the database data files are damaged or missing, the database cannot start normally. You can attempt a tail-log backup using WITH NO_TRUNCATE. This tells SQL Server to back up the log records without trying to access the data files or truncate the inactive portion of the log, which might fail since the database is damaged.

BACKUP LOG ExampleDB
TO DISK = '\\YourBackupShare\Backup\ExampleDB_TailLog_NoTruncate.trn'
WITH NO_TRUNCATE;

Scenario 2: Planned migration

In scenario 2, you’re migrating the database to a new instance and must ensure all transactions are captured. I’ll typically switch the database into SINGLE_USER mode and kill all other connections when doing so.

ALTER DATABASE ExampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Perform this step once your migration window has started and any applications, scheduled tasks, jobs, etc. have been stopped.

Perform the tail-log backup.

BACKUP LOG ExampleDB
TO DISK = '\\YourBackupShare\Backup\ExampleDB_TailLog.trn'
WITH NORECOVERY;

Here’s what happens when you run this backup.

1. The final log records are backed up to ExampleDB_TailLog.trn.
2. The ExampleDB database is put into the RESTORING state.
3. No further transactions can occur in the original ExampleDB database.
4. You can now proceed to restore any subsequent differential/log backups that you haven’t restored yet and, finally, this tail-log backup (using WITH RECOVERY) on the target server.
5. Once restored and recovered, don’t forget to place ExampleDB into MULTI_USER mode on the target server.

Tail-log backup process checklist

Here’s a quick checklist for performing a planned tail-log backup (like for a migration):

• Verify Recovery Model: Ensure the database is in FULL or BULK_LOGGED mode.
• Check Backup History: Confirm a recent full backup exists. Regular log backups should be running.
• Notify Users: Inform users of the planned downtime. Work with all other teams that depend on the database to determine a sufficient maintenance window for the migration. They’ll need to update connection strings, scheduled jobs, SSIS packages, etc., to point to the new database location.
• Restrict Access: Prevent new connections/transactions just before the backup.
• Execute BACKUP LOG … WITH NORECOVERY: Run the command, specifying a clear path and filename.
• Verify Backup File: Ensure the .trn file was created successfully.
• Confirm Database State: Check that the source database is now in the RESTORING state.
• Proceed with Restore: Use the tail-log backup as the final restore step on the target server or for recovery.
• Set database to MULTI_USER: If you’ve placed the source database in SINGLE_USER mode just prior to performing the tail-log backup, the restored database on the target will be in SINGLE_USER mode as well. To allow connections, be sure to switch it to MULTI_USER.

In conclusion

While often overlooked in basic backup discussions, the tail-log backup is a vital tool in the SQL Server DBA’s toolkit. It provides the critical ability to capture the very last transactions before a database restore or migration, minimizing data loss and ensuring the most up-to-date recovery possible. Understanding when and how to use it is key to robust data protection and seamless database migrations.

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.