Tail-log backup in MS SQL

Joe Chan 14 Apr, 2019

MSSQL-Feature-Image

What is Tail-log backup:
It is a log backup.
It is used to backup log after the least log backup.

When will use Tail-log backup:
1. For non-scheduler process
2. For damaged database

How to Tail-log backup in T-SQL:

-- Before run the backup, it is recommanded to make the database as signle user mode.
-- The following command make database named "AdventureWorks2016" into single user mode.
ALTER DATABASE AdventureWorks2016
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Backup Tail-log of database named "AdventureWorks2016" to media set named "COMP_ENCRYPT_LOG" with 3 backup file.
-- Using CONTINUE_AFTER_ERROR for damaged database.
BACKUP LOG AdventureWorks2016 TO
DISK='\\WS01\Sources\Log01-Comp-Encrypt.bak',
DISK='\\WS01\Sources\Log02-Comp-Encrypt.bak',
DISK='\\WS01\Sources\Log03-Comp-Encrypt.bak'
WITH CONTINUE_AFTER_ERROR;
GO

Author: Joe Chan