backup transaction log

Guide to SQL Server Transaction Log Backup

In case you use Microsoft SQL Server you might heard about the Simple and Full database recovery models. Probably you also know that a Simple recovery model allows you to restore data only at the time of the backup, while Full  at any time (you only need to make regular backups of the transaction log). To restore data with the Full recovery model you will need to apply transaction log backups in a specific sequence. This can be done easily using SQL Server Management Studio (SSMS), but only on the SQL Server where the backups were created.

On a productive SQL server, transaction logs is needed to be backed up regularly to avoid overflowing the transaction log file and automatic growth operations. To get a report about the size of the transaction log (in MB) and in percentage terms with the size of the database itself, execute the T-SQL command:




The account that is used to start the backup process must have BACKUP DATABASE and BACKUP LOG permissions. To do this, it is enough to add user to the sysadmin role or in one of the predefined database roles db_owner or db_backupoperator.

There are several ways to create a backup of the transaction log in SQL Server 2016 using SSMS, T-SQL or SQL Server PowerShell.

Tip. You can truncate sql logs and that will free up space in the logical log for reuse of the transaction log.

Backup a Transaction Log Using SQL Server Management Studio

  1. Run SQL Server Management Studio and connect to the desired SQL Server instance;
    sql server connect
  2. In Object Explorer window select your database, right click on the database name and select Tasks -> Backup;
    sql server object explorer back up
  3. Make sure that the database model recovery mode Full or Bulk_Logged is used;
    Tip. Simple recovery model does not support transaction log backups
  4. In the Backup type drop-down list select Transaction Log;
    sql server transaction log
  5. If necessary, create a new backup destination such as Disk, URL or Tape. In our example, a new file was created on a separate drive E:
    sql server destination back up log
  6. To create a backup, click OK;
  7. A message should appear “The backup of database DBNAME complete successfully”.
    log database backup

Backup a Transaction Log Using T-SQL

To create a backup of the DB transaction log using T-SQL, create a new Backup Device named DB1device.
sql server backup devices

To create a backup of the transaction log of the DWConfiguration database on the device DB1device, execute the following command:

BACKUP LOG DWConfiguration

TO DB1backup;


sql server DWConfiguration

Or backup transaction log to one disk file:

BACKUP LOG DWConfiguration TO DISK = 'e:\backup\DWConfiguration.TRN';


Backup Transaction Log Using PowerShell

Another way to create a backup of the transaction log is to use the SQL Server PowerShell cmdlet Backup-SqlDatabase.

Backup-SqlDatabase -ServerInstance SQL2016SP1HOL\MSSQLSERVER -Database DWConfiguration -BackupAction Log

After that, the transaction logs are cleared, and the released space can be overwritten with new transactions.

You may also like:

Add Calendar Permissions in Office 365 via PowerSh... This is a tutorial on how to add calendar permissions in Office 365 for your users via PowerShell. You can add the permissions on a specific user’s ma...
Restore Windows 10 Registry from Backup using Comm... When Windows 10 have some problems with registry files/settings (in case of corruption, accidental deletion etc.), the system offers a simple way to r...
How to Truncate SQL Transaction Logs Transaction logs in SQL Server 2012 tend to grow over time, which can sometimes fill all your available disk space. To avoid this, SQL Server has Trun...
How to Get List of Installed Programs in Windows 1... In this simple guide, we will show you two different ways of how to get a list of installed programs in Windows 10, 8 or Windows 7 using built-in comm...
How to copy files with BITS using PowerShell? If you use local (and global) networks, you might know that files between systems are transferring by using SMB, FTP or HTTP protocols. The problem wi...

Add Your Comment