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
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.
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.
NoteThe 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.
ImportantUse 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.
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
