This blog article shows you how to backup tail log and restore tail log. The while simulation code is below. You are required to have basic knowledge of SQL Server. There is no such command as BACKUP TAIL LOG. The command is BACKUP LOG … WITH NO_TRUNCATE, COPY_ONLY. Beware there are many youtube videos out there on Tail Log is just no going to work. You need to follow the below sequence accordingly.
— Create a database for Tail log back up and restore test
CREATE DATABASE [backupdemo]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’backupdemo’, FILENAME = N’c:\database\backupdemo.mdf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N’backupdemo_log’, FILENAME = N’c:\database\backupdemo_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
— Switch to backupdemo database
USE backupdemo
— Create a table for transaction
CREATE TABLE [Test](
[c1] int identity,
[c2] varchar(100)
)
— Insert 1 record
Insert INTO [Test] VALUES (‘transcation 1’)
— Do a full backup
BACKUP Database [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Full.bak’ WITH INIT
— Insert another 2 records
Insert INTO [Test] VALUES (‘transcation 2’)
Insert INTO [Test] VALUES (‘transcation 3’)
— Do a transaction log backup
BACKUP LOG [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Log.bak’ WITH INIT
— Insert another 2 records but this time no backup before delete the mdf file
Insert INTO [Test] VALUES (‘transcation 4’)
Insert INTO [Test] VALUES (‘transcation 5’)
— Verify the data before crash the database
SELECT * FROM [backupdemo].[dbo].[Test]
— simulate crash, take it offline and delete the mdf file
USE MASTER
Go
Alter database [backupdemo] set single_user with rollback immediate
Go
Alter database [backupdemo] SET OFFLINE
GO
— Delete backupdemo mdf
EXECUTE master.dbo.sp_configure N’show advanced options’, 1; RECONFIGURE
EXECUTE master.dbo.sp_configure N’xp_cmdshell’, 1; RECONFIGURE
EXECUTE master.dbo.xp_cmdshell N’Del c:\database\backupdemo.mdf’
EXECUTE master.dbo.sp_configure N’xp_cmdshell’, 0; RECONFIGURE
EXECUTE master.dbo.sp_configure N’show advanced options’, 0; RECONFIGURE
— Bring crash database back online
Alter database [backupdemo] SET ONLINE
— Backup the tail log. WITH NO_TRUNCATE, COPY_ONLY IS a must
BACKUP LOG [backupdemo] TO DISK = N’c:\database\backup\backupdemo_Log_Tail.bak’
WITH NO_TRUNCATE, COPY_ONLY;
— Restore Full backup with NORECOVERY
Restore database [backupdemo]
FROM DISK = N’c:\database\backup\backupdemo_Full.bak’
WITH
MOVE N’backupdemo’ TO N’c:\database\backupdemo.mdf’,
MOVE N’backupdemo_log’ TO N’c:\database\backupdemo_log.ldf’,
REPLACE, NORECOVERY;
GO
— Restore Log backup with NORECOVERY
Restore LOG [backupdemo]
FROM DISK = N’c:\database\backup\backupdemo_Log.bak’
WITH NORECOVERY;
GO
— Restore tail log backup with NORECOVERY
Restore LOG [backupdemo]
FROM DISK = N’c:\database\backup\backupdemo_Log_Tail.bak’
WITH RECOVERY;
GO
— Verify the data
SELECT * FROM [backupdemo].[dbo].[Test]

Script download: https://github.com/chanmmn/SQLUtilities/tree/master/TailBackupRestoreScript
Reference: https://www.sqlshack.com/tail-log-backup-and-restore-in-sql-server/
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-server-ver15/?WT.mc_id=DP-MVP-36769