Important Backup Options

Important Backup options

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

The backup media is not overwritten if any one of the following conditions is met:

• All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.


• The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.

Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section.

Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.

NOINIT

Indicates that the backup set is appended to the specified disk or tape device, preserving existing backup sets. NOINIT is the default.

The FILE option of the RESTORE command is used to select the appropriate backup set at restore time

STANDBY = undo_file_name

Used only with BACKUP LOG. Backs up the tail of the log and leaves the database in read-only and standby mode. The undo file name specifies storage to hold rollback changes which must be undone if RESTORE LOG operations are to be subsequently applied.

If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it

NOUNLOAD

Specifies the tape is not unloaded automatically from the tape drive after a backup. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.

RESTART

Specifies that SQL Server restarts an interrupted backup operation. The RESTART option saves time because it restarts the backup operation at the point it was interrupted. To RESTART a specific backup operation, repeat the entire BACKUP statement and add the RESTART option. Using the RESTART option is not required but can save time.

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable

NO_TRUNCATE

Allows backing up the log in situations where the database is damaged.