561
Chapter 21: Backup and Recovery Planning
21
BACKUP DATABASE AdventureWorks2012
TO DISK = 'e:\AdventureWorks2012Backup.bak'
WITH
DIFFERENTIAL,
NAME = 'AdventureWorks2012Backup';■ (^) To back up a fi le or fi legroup, list it after the database name. This technique can
help organize backups. For example, for backup purposes, you can design your
database to place static tables in one fi legroup and active tables in the primary
fi legroup.
■ (^) COMPRESSION/NO_COMPRESSION: Overrides the server-level default backup com-
pression. COMPRESSION enables backup compression and performs checksums to
detect media corruptions.
■ (^) CHECKSUM/NO_CHECKSUM: Identical to the Perform Checksum Before Writing to
Media option within Management Studio.
■ (^) STOP_ON_ERROR/CONTINUE_AFTER_ERROR: Identical to Continue on Error option
within Management Studio.
The backup command has numerous additional options:
■ (^) DESCRIPTION: Identical to the Description fi eld within Management Studio.
■ (^) EXPIREDATE: Identical to Management Studio; prevents the backup from being
overwritten before the expiration date.
■ (^) RETAINDAYS: The number of days, as an integer, before SQL Server overwrites the
backup.
■ (^) STATS = percentage: Tells SQL Server to report the progress of the backup
in the percentage increment specifi ed; the default increment is 10 percent. This
option is useful particularly while troubleshooting a failed backup. By using this
option, it gives an idea when the backup is failing. Also, for huge databases this
gives an idea of the percentage of backup completed and the amount remaining.
■ (^) BLOCKSIZE: Sets the physical block size in bytes. The default is 65536 bytes for
tape devices and 512 otherwise. This option is usually not required because backup
automatically selects the correct block size of the device. If a backup to disk will
later be copied to a CD/RW, try a block size of 2048.
■ (^) MEDIANAME: Specifi es the name of the media volume. This option serves as a safety
check: If the backup is added to the media, the name must match.
■ (^) MEDIADESCRIPTION: Writes an optional media description.
■ (^) MediaPassword: Creates an optional media password that applies to the entire
medium (disk fi le or tape). The fi rst time the medium is created, the password can
be set. If the password is specifi ed when the medium is created, it must be specifi ed
every subsequent time the backup medium is accessed to add another backup or to
restore.
c21.indd 561c21.indd 561 7/31/2012 9:22:48 AM7/31/2012 9:22:48 AM
http://www.it-ebooks.info