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.

  • 2
    I disagree. I think this is accurate enough. The Docs explicitly explain the difference as one changing the media and the other changing the backup header. One replaced the file the other invalidates any striped media set. – clifton_h Oct 12 '18 at 13:02
  • @clifton_h comment deleted as the OP has been clarified. – George.Palacios Oct 12 '18 at 13:46

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.
| improve this answer | |
  • When you say "completely obliterating the backup file, or tape", do you mean it can impact the entire tape. So if tape has 5 media sets then creating a new backup with the FORMAT option will destroy entire tape? Please guide. – variable Oct 12 '18 at 13:12
  • Yes, backing up to tape using WITH FORMAT completely eradicates the content of the tape. It overwrites the header section of the tape, rendering the contents after that section unreadable. – Max Vernon Oct 12 '18 at 13:14
  • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file... – variable Oct 12 '18 at 13:16
  • "So if the tape were a disk drive" no. A Disk backup device is a single file, not an entire disk. For Disk backups you almost always just use a new file for each backup. – David Browne - Microsoft Oct 12 '18 at 13:22

Complementing to what Max answered, 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 :-)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.