Share via

  • Article

Back Up Database (Options Page)

Use the Options page of the Back Up Database dialog box to view or modify database backup options.

To create a backup by using SQL Server Management Studio

Important

You can define a database maintenance plan to create database backups. For more information, see Database Maintenance Plans and How to: Start the Maintenance Plan Wizard (SQL Server Management Studio).

Note

When you specify a backup task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and then selecting a destination for the script.

Options

Overwrite media

The options of the Overwrite media panel control how the backup is written to the media.

Note

For information about media sets, see Media Sets, Media Families, and Backup Sets.

  • Back up to the existing media set
    Back up a database to an existing media set. Selecting this option button activates three options.

    Choose one of the following options:

    • Append to the existing backup set
      Append the backup set to the existing media set, preserving any prior backups.

    • Overwrite all existing backup sets
      Replace any prior backups on the existing media set with the current backup.

  • Check media set name and backup set expiration
    Optionally, if backing up to an existing media set, require the backup operation to verify the name and the expiration date of the backup sets.

  • Media set name
    If Check media set name and backup set expiration is selected, optionally, specify the name of the media set to be used for this backup operation.

  • Back up to a new media set, and erase all existing backup sets
    Use a new media set, erasing the previous backup sets.

    Clicking this option activates the following options:

    • New media set name
      Optionally, enter a new name for the media set.

    • New media set description
      Optionally, enter a meaningful description of the new media set. This description should be specific enough to communicate the contents accurately.

Reliability

The options of the Transaction log panel control error management by the backup operation.

  • Verify backup when finished
    Verify that the backup set is complete and that all volumes are readable.

  • Perform checksum before writing to media
    Verify the checksums before writing to the backup media. Selecting this option is equivalent to specifying the CHECKSUM option in the BACKUP statement of Transact-SQL. Selecting this option may increase the workload, and decrease the backup throughput of the backup operation. For information about backup checksums, see Detecting and Coping with Media Errors During Backup and Restore.

  • Continue on error
    The backup operation is to continue even after encountering one or more errors.

Transaction log

The options of the Transaction log panel control the behavior of a transaction log backup. These options are relevant only under the full recovery model or bulk-logged recovery model. They are activated only if Transaction log has been selected in the Backup type field on the General page of the Back Up Database dialog box.

Note

For information about transaction log backups, see Working with Transaction Log Backups.

  • Truncate the transaction log
    Back up the transaction log and truncate it to free log space. The database remains online. This is the default option.

    For more information, see Transaction Log Truncation.

  • Back up the tail of the log, and leave the database in the restoring state
    Back up the tail of the log and leave the database in a restoring state. This option creates a tail-log backup, which backs up logs that have not yet been backed up (the active log), typically, in preparation for restoring a database. The database will be unavailable to users until it is completely restored.

    Selecting this option is equivalent to specifying WITH NO_TRUNCATE, NORECOVERY in a BACKUP statement (Transact-SQL). For more information, see Tail-Log Backups.

Tape drive

The options of the Tape drive panel control tape management during the backup operation. These options are activated only if Tape has been selected in the Destination panel on the General page of the Back Up Database dialog box.

Note

For information about how to use tape devices, see Backup Devices.

  • Unload the tape after backup
    After the backup is complete, unload the tape.

  • Rewind the tape before unloading
    Before unloading the tape, release and rewind it. This is enabled only if Unload the tape after backup is selected.

Compression

SQL Server 2008 Enterprise (or a later version) supports backup compression.

  • Set backup compression
    In SQL Server 2008 Enterprise (or a later version), select one of the following backup compression values:

    Use the default server setting

    Click to use the server-level default.

    This default is set by the backup compression default server-configuration option. For information about how to view the current setting of this option, see How to: View or Change the backup compression default Option (SQL Server Management Studio).

    Compress backup

    Click to compress the backup, regardless of the server-level default.

    Important noteImportant
    By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

    Do not compress backup

    Click to create an uncompressed backup, regardless of the server-level default.