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

  • Article

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.

  2. Optionally, specify:

    • The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

    • The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.

    • The FORMAT clause when you are using media for the first time to initialize the backup media and rewrite any existing media header.
      The INIT clause is not required if the FORMAT clause is specified.

      Important

      Use extreme caution when you are using the FORMAT or INIT clauses 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 AdventureWorks database to disk, by using FORMAT to create a new media set.

'C:\AdventureWorks.Bak'
USE AdventureWorks
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\MSSQL\BACKUP\AdventureWorks.Bak'
   WITH FORMAT,
   NAME = 'Full Backup of AdventureWorks'
GO
-- Optionally, create a logical backup device, 
-- AdventureWorks_Backup, for this backup file.
USE master
EXEC sp_addumpdevice 'disk', 'AdventureWorks_Backup', 
'C:\MSSQL\BACKUP\AdventureWorks.Bak'
B. Backing up to a tape device

The following example backs up the complete MyAdvWorks database to tape.

USE MyAdvWorks
GO
BACKUP DATABASE MyAdvWorks
   TO TAPE = '\\.\Tape0'
   WITH FORMAT,
   NAME = 'Full Backup of MyAdvWorks'
GO
-- Optionally, create a logical backup device, 
-- AdventureWorks_Backup, for this backup tape.
USE master
GO
EXEC sp_addumpdevice 'tape', 'MyAdvWorks_Bak', '\\.\tape0'

See Also

Tasks

How to: Back Up a Database (SQL Server Management Studio)

Concepts

Appending to Existing Backup Sets
Full Database Backups
Considerations for Backing Up the master Database
Considerations for Backing Up the model and msdb Databases
Overwriting Backup Sets
Differential Database Backups
Working with Transaction Log Backups

Other Resources

BACKUP (Transact-SQL)
sp_addumpdevice (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance