SQL Server DATABASE Transaction LOG BACKUP Using Transact-SQL

A database developer and Database Administrator should be familiar with these methods.

I have already rovided a brief introduction of database backups and Transaction Logs in my previous article SQL Server DATABASE BACKUP With Multiple Methods Using Transact-SQL and How to Track Database Transaction Log in SQL Server 2012.

About SQL Server Database Transaction Log BACKUP

You must have created at least one full backup before you create any Transaction Log backups, the Transaction Log can be backed up at any time unless the log is already being backed up.

We can create a SQL Server DATABASE LOG BACKUP with multiple methods using TSQL as in the following:

• Transaction Log backups to disk
• LOG backups with progress stats
• LOG Backup with description
• Mirrored LOG backup
• LOG Backup with multiple options
• LOG Backup with [Media Name]
• LOG Backup with Expiration Date

Now I am creating a SQL Server database LOG backup using the preceding method of the DBAWorks database.

Transaction Log backups to disk

A Transaction Log is also called a database log. It can record any action by the database and store the recording results in a separate file. Now use the database "DBAWorks" to create the backup.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks.TRN'
GO

LOG backups with progress stats

Stats display the progress of the LOG backup. By default progress is shown after every 10%. You can set the stats value as you want.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks_DefaultStats.TRN'
WITH STATS
GO

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks_StatsVlue.TRN'
WITH STATS=5
GO

LOG Backup with description

This option gives the backup name. The maximum size is 255 characters; we can use that, this can later be used with some of the restore commands to see what is contained in the backup.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks_Desc.TRN'
WITH DESCRIPTION = 'Log backup for DBAWorks'
GO

Mirrored LOG backup

A Mirrored LOG backup allows you to create multiple copies of the backups, preferably to various locations.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks.TRN'
MIRROR TO DISK = 'D:\backup\NewBackup\DBAWorks_mirror.TRN'
WITH FORMAT
GO

LOG Backup with multiple options

You can define multiples options in it as in the following:

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks1.TRN'
MIRROR TO DISK = 'D:\backup\NewBackup\DBAWorks_mirror1.TRN'
WITH DESCRIPTION = 'Log backup for DBAWorks', FORMAT, STATS
GO

LOG Backup with [Media Name]

A database backup to disk using a format to create a new media set.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\DBAWorks_Media.TRN'
WITH FORMAT,
MEDIANAME = 'my_SQLServerBackups',
NAME = Log Backup of DBAWorks';
GO

LOG Backup with Expiration Date

You can specify an expiration date to create the backup.

USE [Master]
GO
BACKUP LOG DBAWorks
TO DISK = 'D:\backup\NewBackup\[DBAWorks_date].TRN'
WITH EXPIREDATE = '01/31/2015'
GO

You can see the following query of how many backups have been created of the database:

SELECT a.database_name ,
b.physical_device_name ,
a.type ,
a.expiration_date ,
a.name ,
a.description
FROM msdb.dbo.backupset a
LEFT JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id

You can also see in the following path: