A tail-log backup captures the log records that have not been backed up yet, so a restore loses nothing. Take one before restoring over a database, or after a failure if the log file can still be read. The database must be in FULL or BULK_LOGGED recovery. Tested on SQL Server 2017 and 2025.

In SSMS: right-click the database, Tasks, Back Up, set Backup type to Transaction Log, then on Media Options tick “Back up the tail of the log, and leave the database in the restoring state”. That is the same as the first statement below.

-- Run step 1 OR step 2, not both.

-- 1. Tail-log backup when the database is still online
--    (the last log backup before a restore; leaves the database RESTORING so nobody can change it)
BACKUP LOG [MyDB]
TO DISK = N'D:\Backup\MyDB_tail.trn'
WITH NORECOVERY, CHECKSUM, COMPRESSION, STATS = 10;

-- 2. Tail-log backup when the data files are damaged or missing
--    (the log file must still be readable)
BACKUP LOG [MyDB]
TO DISK = N'D:\Backup\MyDB_tail.trn'
WITH CONTINUE_AFTER_ERROR, NO_TRUNCATE, CHECKSUM, STATS = 10;

-- 3. Check the backup is readable
RESTORE VERIFYONLY FROM DISK = N'D:\Backup\MyDB_tail.trn' WITH CHECKSUM;

-- 4. Restore order: full, then latest diff, then each log, then the tail with RECOVERY
-- RESTORE DATABASE [MyDB] FROM DISK = N'D:\Backup\MyDB_full.bak' WITH NORECOVERY, REPLACE;
-- RESTORE LOG      [MyDB] FROM DISK = N'D:\Backup\MyDB_log1.trn' WITH NORECOVERY;
-- RESTORE LOG      [MyDB] FROM DISK = N'D:\Backup\MyDB_tail.trn' WITH RECOVERY;