Create a transaction log backup

Before creating a transaction log backup, you must perform a full database backup. Optionally, you can also create one or more differential backups after the full backup. Afterward, you can perform transaction log backups at regular intervals to capture ongoing changes.

This topic describes how to create a transaction log backup in SQL Server by using the Backup wizard in dbForge Studio for SQL Server.

Limitations

  • A transaction log backup requires a full backup as its base.
  • To restore from a transaction log backup, you must have the most recent full backup, any differential backups created afterward, and all transaction log backups in sequence up to the point you want to restore.
  • You can’t create a transaction log backup when the database is offline, read-only, or in restoring, suspect, or emergency mode.

Permissions

To create a transaction log database backup, you must have at least one of the following permissions or role memberships:

  • BACKUP DATABASE and BACKUP LOG permissions.
  • Membership in the db_owner fixed database role.
  • Membership in the sysadmin fixed server role.
  • Membership in the db_backupoperator fixed database role.

Prerequisite

  • Connect to the required server instance.
  • Create at least one full database backup.

Back up a database

1. Open the Backup wizard in one of these ways:

  • In the top menu, select Database > Tasks > Back Up.
  • In Database Explorer, right-click the connection name or required database and select Tasks > Back Up.
  • On the Start Page, select Administration > Back Up.

2. On the General page, configure the general backup options:

2.1. In Connection, select the server connection for the database you want to back up.

2.2. In Database, select the database you want to back up.

2.3. In Backup type, select TransactionLog.

2.4. In Backup to disk, click Add to change the path to the destination folder to store a backup file.

2.5. To create a copy-only backup, select Copy-only backup.

2.6. In Backup to disk, click Add to change the path to the destination folder to store a backup file.

Tip

To remove a destination file, click Remove.

General options in the Backup wizard

3. In the Browse Files dialog, choose a destination folder to store the file:

3.1. In Select the file, select a backup file.

3.2. In Selected path, view the path to the backup file.

Note

The default path to store the backup file is
C:\Program Files\Microsoft SQL Server\<instance_name>\MSSQL\Backup\file_name.bak.

3.3. In FileName, keep the default name or enter a new one.

3.4. Click OK to save the changes.

Browse Files dialog

4. On the Media options page, configure the media options:

  • Under Overwrite Media, choose how the backup should be written to the media.
  • Optional: Under Reliability, configure how errors should be handled.
  • Under Transaction Log, choose how to handle the transaction log during a restore or backup operation.

Set up general media options

5. On the Backup options page, configure the backup options:

5.1. In Name, specify the backup name.

5.2. In Description, specify the backup description.

5.3. Under Backup set will expire, select one of the following options:

  • After: Specify the number of days after which the backup expires and can be overwritten.
  • On: Specify the date when the backup expires.

5.4. Select Compress backup to reduce the size of the backup file, regardless of the server-level default.

6. To create an encrypted backup, select Encrypt backup, then select an encryption algorithm and a certificate or asymmetric key from a list.

Note

The Encrypt backup option is available when the Back up to a new media set and erase all existing backup sets option is selected.

Tip

It is recommended to back up your certificate or keys and store them in a different location from the backup you encrypted.

Select backup options

7. Click Back Up.

When the operation is complete, you can view its result on the Finish page.

8. To back up the database again, click Backup Again.

9. Click Finish to close the wizard.

Transaction log backup is finished

For more information on configuring SQL Server backups on a Windows system, see How to Set Up Every Day Database Auto Backup in SQL Server.

Backup wizard options

Media options

The table provides a list of options available on the Media options page of the wizard.

Overwrite Media

Name Description
Back up to the existing media set Adds your backup to an existing media set, which can contain multiple backup sets.
Append to the existing backup set Adds the new backup to the end of the current backup set. Existing backups are preserved.
Overwrite all existing backup sets Replaces the current contents of the media set with new data. All previous backups on this set will be lost.
Check media set name and backup set expiration (checkbox) Verifies the name and the expiration date of the backup sets.
Back up to a new media set and erase all existing backup sets Creates a new media set, removing all existing backup sets on the media.
Media set name and Media set description Specifies a name and description for the current media set.

Reliability

Name Description
Verify backup when finished Verifies the backup to ensure it can be read and restored correctly.
Perform checksum before writing to media Adds a checksum for each page to detect corruption during the write operation.
Continue on error Continues a backup operation if an error is encountered.

Transaction Log

Name Description
Truncate the transaction log Back up the transaction log and removes the inactive part of the transaction log to free log space.
Back up the tail of the log and leave the database in the restoring state Includes all recent transactions to minimize data loss before restoring the database.

Backup options

The table provides a list of options available on the Backup options page of the wizard.

Backup set

Name Description
Name Specifies a backup name.
Description Specifies a backup description.
Backup set expiration Specifies when the backup set can be overwritten:
  • After: Specify the number of days after which the backup expires and can be overwritten.
  • On: Specify the date when the backup expires and can be overwritten.
Compress backup Reduces the size of the backup file, regardless of the server-level default.

Encrypted options

Name Description
Encrypt backup Creates an encrypted backup to protect data from unauthorized access during storage and transfer.
Algorithm Specifies an encryption algorithm.
Certificate or Asymmetric Key Identifies a certificate or asymmetric key.