Skip to content

SQL Server Transaction Log Backups

    A transaction log backup contains all transaction log records that have been made between the last transaction log backup or the first full backup and the last log record that is created upon completion of the backup process. The transaction log backup allows to restore a database to a particular point-in-time before the failure has occurred. It is incremental, meaning that in order to restore a database to a certain point-in-time, all transaction log records are required to replay database changes up to that particular point-in-time. Please note that transaction log backup is available only for full or bulk-logged recovery models.

    This example creates a transaction log backup for the AdventureWorks2012 database to the previously created named backup device, MyAdvWorks_FullRM_log1.

    BACKUP LOG AdventureWorks2012  
       TO MyAdvWorks_FullRM_log1;  

    Using PowerShell

    Set up and use the SQL Server PowerShell Provider. Use the Backup-SqlDatabase cmdlet and specify Log for the value of the -BackupAction parameter.

    The following example creates a log backup of the database to the default backup location of the server instance Computer\Instance.

    Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Log

    SQL Server Management Studio

    • Right click on the database name
    • Select Tasks > Backup
    • Select “Transaction Log” as the backup type
    • Select “Disk” as the destination
    • Click on “Add…” to add a backup file and type “C:\AdventureWorks.TRN” and click “OK”
    • Click “OK” again to create the backup