How to create a database backup (Transact-SQL)

To create a database backup

1. Execute the BACKUP DATABASE statement to create the database backup, specifying:
   • The name of the database to back up.
     
     
   • The backup device where the database backup will be 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 using media for the first time to completely 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 using the FORMAT or INIT clauses of the BACKUP statement, as this will destroy any backups previously stored on the backup media.

Examples

This example backs up the entire MyNwind database to tape:

USE MyNwind
GO
BACKUP DATABASE MyNwind
   TO TAPE = '\\.\Tape0'
   WITH FORMAT,
   NAME = 'Full Backup of MyNwind'
GO

See Also

sp_addumpdevice

Database Backups

Appending Backup Sets

Differential Database Backups

Backing Up the master Database

Deleting a Database

Backing Up the model, msdb, and distribution Databases

Overwriting Backup Media

BACKUP

Reducing Recovery Times

Transaction Log Backups

Initializing Backup Media