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:

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  B-tree Structure is Invalid Solutions

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  Accessing Hyper-V Virtual Machine Console Using RDCMan

You may also like:

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...
Grant Full Access to All Mailboxes in Exchange 200... This is a short tutorial on how to add full access to all mailboxes in Exchange 2007 and 2010 for a an additional user. This can come in handy when sa...
Add Calendar Permissions in Office 365 via Powersh... This is a tutorial on adding calendar permissions in Office 365 for your users via Powershell. You can add permissions onto a specific mailbox, or you...
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...
Applications deployment by Make and Model using MD... In this article we will show you guys how to configure your SQL database within MDT 2013 Update 1 and configure it to start using the Make and Model s...