Search
Home
Articles
Backup
Books
Certification
FAQ
Products
Replication
Scripts
Seminars
Training
TSQL
MSDN Fourms
Fort Worth SSUG
Oklahoma City SSDG
Resume
Champion Valley Pens

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

The entire structure of the backup command can be boiled down to the following:

backup database <database> to <device> with <options>

The options can be decribed as follows:

Option Description
Blocksize The size of the blocks to write the backup
Description Descriptive text for the contents of the backup
Expire date Date the backup will expire and be allowed to be overwritten
Retain days Number of days before the backup can be overwritten
Format / noformat Specifies whether or not to reformat the backup media and replace the media haeader
Init / noinit Init specifies that the database should be the first one in the backup file and overwrites any existing backups. Noinit will cause the database to be appended to the backup file.
Media description Specifies a name for the backup media
Media name Specifies a media name
Name Specifies the name to refer to the backup set
Noskip /skip Noskip forces SQL Server to read the media header to determine if the backup can be overwritten. Skip prevents the reading of the media header and will allow a backup to be overwritten.
Nounload / unload Specified for tape devices. By default, the tape will eject at the end of each database backup. Specifying nounload prevents the tape from being ejected so that a subsequent database can be written. Unload will explicitly eject a tape.
Restart Can only be used for tape backups. It allows an interrupted backup to be restarted at the point where it left off.
Stats Specifies the percentage intervals at which to show the progress of the backup operation.

Full backup

A full database backup is one of the most common backups that can be performed. This causes all used data pages to be written to the backup device. In most environments, a full database backup is taken each evening. The following lists a series of backup commands for different situations.

Backup to an explicit disk device and begin a new backup set.

EXEC sp_addumpdevice 'disk', 'diskbackup',

'c:\mssql7\backup\diskbackup.bak'

backup database pubs to diskbackup with format

Backup to an undefined network disk device and overwrite the existing backups

backup database pubs to disk = ‘\\myserver\dbbackups\pubs.bak’ with init

Backup to a tape device and leave the tape in the drive

exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0'

backup database pubs to tapebackup with nounload

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.