Create a transaction log backup

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

Prerequisite

  • Connect to the required server instance.

Back up a database

1. Open the Backup wizard by doing one of the following:

  • On 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 of the wizard, set up the following general backup options:

  • Connection: Select the server connection for the database you want to back up.
  • Database: Select the database you want to back up.
  • Backup type: Set a backup type to Transaction Log.
  • Recovery model: View the recovery model of the selected database.

Note

The Copy-only backup checkbox is available only for full and transaction log backups.

General options in the Backup wizard

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

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

  • In Select the file, select a backup file.
  • In Selected path, view the path to the backup file.

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

  • In Files of type, the Backup Files (*.bak; *.trn ) are selected by default.
  • In FileName, the name is derived from the database name. To set a different name for a backup file, specify it in this field.
  • Click OK to save the changes.

Browse Files dialog

Tip

To remove a destination file, click Remove.

5. On the Media options page, set up general media options:

5.1. Under Overwrite Media, select one of the following options:

  • Back up to the existing media set: Adds your backup to an existing media set, which can contain multiple backup sets. Selecting this option makes the following options available:
    • 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): Ensures the media set name matches the expected name, and the backup set hasn’t expired.

  • Back up to a new media set and erase all existing backup sets: Creates a new media set and removes all existing backup sets on the media (like a reset). Selecting this option makes the following options available:

    • Media set name and Media set description: Allow you to specify a name and description for the media set.

5.2. Optional: Under Reliability, configure additional integrity checks:

  • Verify backup when finished: Checks 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.

5.3. Under Transaction Log, select the checkbox next to the options to handle the transaction log during a restore or backup operation:

  • Truncate the transaction log: Removes the inactive part of the transaction log.
  • Back up the tail of the log and leave the database in the restoring state: Captures all recent transactions to minimize data loss before restoring the database.

Set up general media options

6. On the Backup options page, select a backup set and choose your encryption options.

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

7. Select the Encrypt backup checkbox to create an encrypted backup to protect data from unauthorized access during storage and transfer:

  • Select an encryption algorithm.
  • Choose 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

8. To run the backup operation, click Back Up.

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

Tip

To back up the database again, click Backup Again.

9. To close the wizard, click Finish.

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.