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:

DBCC SQLPERF(LOGSPACE);

GO

logspace

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.

READ ALSO  Removing Old and Unused Drivers from Driver Store using 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;

GO

sql server DWConfiguration

Or backup transaction log to one disk file:

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

GO

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.

READ ALSO  How to Get Windows 10 User Login History Using PowerShell?
Cyril Kardashevsky

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.