562
Part V: Enterprise Data Management
Avoid using the MediaPassword option because this feature will be removed in the future release of SQL Server.■ INIT/NOINIT: Initializes the tape or disk fi le, thus overwriting all existing
backup sets in the medium. SQL Server can prevent initialization if any of the
backups in the medium have not expired or still have the number of retaining days.
NOINIT is the default.
■ NOSKIP/SKIP: This option “skips” the backup-name and backup-date checking
that normally prevents overwriting backups. NOSKIP is the default.
■ NOFORMAT/FORMAT: FORMAT writes a new media header on media volumes used for
backup and overwrites the existing backup sets; thereby the existing contents of
the volume become unusable. NOFORMAT (default behavior) preserves the existing
media header and backup sets. FORMAT automatically includes SKIP and INIT.
The last options apply only when backing up to tape:■ (^) REWIND/NOREWIND: REWIND directs SQL Server to rewind the tape. The default is
to REWIND.
■ (^) UNLOAD/LOAD: UNLOAD automatically rewinds and unloads the tape. This is the
default until the user session specifi es load.
■ (^) RESTART: This option has no effect. This is there for compatibility with previous
versions of SQL Server.
Verifying the Backup with Code
Management Studio’s backup includes an option to verify the backup, and the T-SQL
Backup command does not. Management Studio actually calls the T-SQL
RESTORE VERIFYONLY command after the backup to perform the verifi cation:
RESTORE VERIFYONLY
FROM DISK = 'e:\AdventureWorks2012Backup.bak'
Result:
The backup set is valid.
The verifi cation has a few options, such as Eject Tape After Backup. Most of these verifi ca-
tion options are for tapes and are self-explanatory.
RESTORE VERIFYONLY does not actually restore the database. It checks only if the backup is complete and is read-
able. By default it checks the backup checksums if they are present and proceeds without verifi cation if they are not
present. Although this is a quick way to check if the backup set is complete and readable, it is not a replacement for
actually performing a restore to see if the backup is valid because this check does not verify the structure of the data
contained in the backup volumes. The only way to truly test and validate your backups is to perform a restore.
c21.indd 562c21.indd 562 7/31/2012 9:22:48 AM7/31/2012 9:22:48 AM
http://www.it-ebooks.info