5

I understand that FORMAT looks at mediaset level and if set then creates a new media set so if there are any existing backupsets then they will no longer be available.

Also, INIT looks at backupset level and if set then overwrites the existing backupset, so if there are any existing backupset then they will no longer be available. Will it impact that backupset or all backupsets on the media set?

So how do these options work? I am trying to get an use case scenario to understand this better.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
variable
  • 3,590
  • 4
  • 37
  • 100

2 Answers2

5

FORMAT recreates the header from scratch, effectively completely obliterating the backup file, or tape. If the backup file or tape is corrupted, this option ignores the corruption, and enables you to reuse the file or tape. From the Microsoft Docs:

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.

INIT rewrites field values in the header info. If the device is corrupt, this may not correct the issue. Also from the Microsoft Docs:

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.

Although you can certainly use both options in the same backup statement, the FORMAT option negates the need for INIT. INIT by itself doesn't reformat the entire backup media, and as noted above won't overwrite backup sets in the media if they haven't expired, or if the backup set name doesn't match the existing backup set.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
1

One case that I found where you need to use both FORMAT and INIT is when TDE is enabled for your database.

You can see my question - Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used and answers.

Apparently, it was a bug in sql server which the PG team confirmed :-)

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Kin Shah
  • 62,545
  • 6
  • 124
  • 245