對備份儲存媒介進行格式化

標籤：

今天在進行資料庫檔案備份時，SQL Server拋出錯誤：

Executed as user: NT SERVICE\SQLSERVERAGENT. The backup data at the end of "xxxx.bak" is incorrectly formatted.

Backup sets on the media might be damaged and unusable.

To determine the backup sets on the media, use RESTORE HEADERONLY.

To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. [SQLSTATE 42000] (Error 3266)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

 

需要對Media Set 進行格式，Init 不會對media header進行初始化，而format是格式化整個media，這兩個Option都會使資料庫檔案backup不可用，必須謹慎。

{ NOINIT | INIT }

Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

NOINIT

Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

• Any backup set has 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 option, earlier in this section.

To override these checks, use the SKIP option.

{ NOFORMAT | FORMAT }

Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.

NOFORMAT

Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior.

FORMAT

Specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

Specifying FORMAT implies SKIP; SKIP does not need to be explicitly stated.

 Use FORMAT carefully. Formatting any volume of a media set renders the entire media set unusable. For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless

 

參考doc：

BACKUP (Transact-SQL)

Media Sets, Media Families, and Backup Sets (SQL Server)

對備份儲存媒介進行格式化