Share via

  • Article

How to: Create a Full Database Backup (Transact-SQL)

This topic explains how to create a full database backup using a BACKUP DATABASE statement.

To create a full database backup

  1. Execute the BACKUP DATABASE statement to create the full database backup, specifying:

    • The name of the database to back up.

    • The backup device where the full database backup is written.

    The basic Transact-SQL syntax for a full database backup is:

    BACKUP DATABASE database

    TO backup_device [ ,...n ]

    [ WITH with_options [ ,...o ] ] ;

    Option

    Description

    database

    Is the database that is to be backed up.

    backup_device [ ,...n ]

    Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option:

    { DISK | TAPE } =physical_backup_device_name

    For more information, see Backup Devices.

    WITH with_options [ ,...o ]

    Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2.

  2. Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP (Transact-SQL).

    • Basic backup set WITH options:

      • { COMPRESSION | NO_COMPRESSION }
        In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.

        Note

        The server-level default is set by using the backup compression default option.

      • DESCRIPTION = { 'text' | **@**text_variable }
        Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.

      • NAME = { backup_set_name | **@**backup_set_name_var }
        Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.

    • Basic backup set WITH options:

      By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this, use the NOINIT option. For more information, see Appending to Existing Backup Sets.

      Alternatively, to format the backup media, use the FORMAT option:

      • FORMAT [ , MEDIANAME**=** { media_name | **@**media_name_variable } ] [ , MEDIADESCRIPTION = { text | **@**text_variable } ]
        Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.

        Important

        Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.

Example

A. Backing up to a disk device

The following example backs up the complete AdventureWorks2008R2 database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

B. Backing up to a tape device

The following example backs up the complete AdventureWorks2008R2 database to tape, appending the backup to the previous backups.

USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

C. Backing up to a logical tape device

The following example creates a logical backup device for a tape drive. The example then backs up the complete AdventureWorks2008R2 database to that device.

-- Create a logical backup device, 
-- AdventureWorks2008R2_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'AdventureWorks2008R2_Bak_Tape', '\\.\tape0'; 
USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
   TO AdventureWorks2008R2_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'AdventureWorks2008R2_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0', 
      NAME = 'Full Backup of AdventureWorks2008R2';
GO