Share via

Back Up Database (General Page)

  • Article

Use the General page of the Back Up Database dialog box to view or modify settings for a database back up operation.

For more information about basic backup concepts, see Backup Overview (SQL Server).

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.

To use SQL Server Management Studio to create a backup

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).

To create a partial backup or copy-only backup

  • For partial or copy-only backups, you must use the Transact-SQL BACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

Options

Source

The options of the Source panel identify the database and specify the backup type and component for the back up operation.

  • Database
    Select the database to back up.
  • Recovery model
    View the recovery model (SIMPLE, FULL, or BULK_LOGGED) displayed for the selected database.

  • Backup type
    Select the type of backup you want to perform on the specified database.

    Backup type Available for Restrictions

    Full

    Databases, files, and filegroups

    On the master database, only full backups are possible.

    Under the Simple Recovery Model, file and filegroup backups are available only for read-only filegroups.

    Differential

    Databases, files, and filegroups

    Under the Simple Recovery Model, file and filegroup backups are available only for read-only filegroups.

    Transaction Log

    Transaction logs

    Transaction log backups are not available for the Simple Recovery Model.

  • Backup component
    Select the database component to be backed up. If Transaction Log is selected in the Backup type list, this option is not activated.

    Select one of the following option buttons:

    Database

    Specifies that the entire database be backed up.

    Files and filegroups

    Specifies that the specified files and/or filegroups be backed up.

    Selecting this option, opens the Select Filegroups and Files dialog box. After you select the filegroups or files you want to back up and click Ok, your selections appear in the Filegroups and files box.

Backup set

The options of the Backup set panel allow for you to specify optional information about the backup set created by the back up operation.

  • Name
    Specify the backup set name. The system automatically suggests a default name based on the database name and the backup type.

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

  • Description
    Enter a description of the backup set.

  • Backup set will expire
    Choose one of the following expiration options.

    After

    Specify the number of days that must elapse before this backup set expires and can be overwritten. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

    The default value for backup expiration is the value set in the Default backup media retention (in days) option. To access this, right-click the server name in Object Explorer and select Properties; then click the Database Settings page of the Server Properties dialog box.

    On

    Specify a specific date when the backup set expires and can be overwritten.

Destination

The options of the Destination panel allow for you to specify the type of backup device for the back up operation and find an existing logical or physical backup device.

Note

For information about SQL Server backup devices, see Backup Devices.

  • Back up to
    Select one of the following types of media to which to back up. The destinations you select appear in the Back up to list.

    Disk

    Backs up to disk. This may be a system file or a disk-based logical backup device created for the database. The currently selected disks appear in the Back up to list. You can select up to 64 disk devices for your backup operation.

    Tape

    Backs up to tape. This may be a local tape drive or a tape-based logical backup device created for the database. The currently selected tapes appear in the Back up to list. The maximum number is 64. If there are no tape devices attached to the server, this option is deactivated. The tapes you select are listed in the Back up to list.
  • Add
    Adds a file or device to the Back up to list. You can back up to 64 devices simultaneously on a local disk or remote disk. To specify a file on a remote disk, use the fully qualified universal naming convention (UNC) name. For more information, see Backup Devices.
  • Remove
    Removes one or more currently selected devices from the Back up to list.
  • Contents
    Displays the media contents for the selected device.

See Also

Other Resources

How to: Back Up a Transaction Log (SQL Server Management Studio)
How to: Back Up Database Files and Filegroups (SQL Server Management Studio)
How to: Define a Logical Backup Device for a Disk File (SQL Server Management Studio)
How to: Define a Logical Backup Device for a Tape Drive (SQL Server Management Studio)
Overview of the Recovery Models

Help and Information

Getting SQL Server 2005 Assistance