Database backup - Use case of FORMAT and INIT

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
2
down vote

favorite












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.










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 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
    1 hour ago










  • @clifton_h comment deleted as the OP has been clarified.
    – George.Palacios
    51 mins ago
















up vote
2
down vote

favorite












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.










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 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
    1 hour ago










  • @clifton_h comment deleted as the OP has been clarified.
    – George.Palacios
    51 mins ago












up vote
2
down vote

favorite









up vote
2
down vote

favorite











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.










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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.







sql-server backup






share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 26 mins ago









MDCCL

6,34731740




6,34731740






New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 1 hour ago









variable

1698




1698




New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







  • 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
    1 hour ago










  • @clifton_h comment deleted as the OP has been clarified.
    – George.Palacios
    51 mins ago












  • 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
    1 hour ago










  • @clifton_h comment deleted as the OP has been clarified.
    – George.Palacios
    51 mins ago







2




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
1 hour ago




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
1 hour ago












@clifton_h comment deleted as the OP has been clarified.
– George.Palacios
51 mins ago




@clifton_h comment deleted as the OP has been clarified.
– George.Palacios
51 mins ago










2 Answers
2






active

oldest

votes

















up vote
3
down vote













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.






share|improve this answer






















  • Please add the reference to the doc pages. There are useful remarks related to these two subjects
    – clifton_h
    1 hour ago










  • 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
    1 hour ago










  • 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
    1 hour ago










  • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
    – variable
    1 hour ago










  • "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
    1 hour ago

















up vote
0
down vote













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 :-)






share|improve this answer




















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );






    variable is a new contributor. Be nice, and check out our Code of Conduct.









     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219960%2fdatabase-backup-use-case-of-format-and-init%23new-answer', 'question_page');

    );

    Post as a guest






























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    3
    down vote













    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.






    share|improve this answer






















    • Please add the reference to the doc pages. There are useful remarks related to these two subjects
      – clifton_h
      1 hour ago










    • 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
      1 hour ago










    • 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
      1 hour ago










    • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
      – variable
      1 hour ago










    • "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
      1 hour ago














    up vote
    3
    down vote













    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.






    share|improve this answer






















    • Please add the reference to the doc pages. There are useful remarks related to these two subjects
      – clifton_h
      1 hour ago










    • 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
      1 hour ago










    • 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
      1 hour ago










    • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
      – variable
      1 hour ago










    • "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
      1 hour ago












    up vote
    3
    down vote










    up vote
    3
    down vote









    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.






    share|improve this answer














    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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited 1 hour ago

























    answered 1 hour ago









    Max Vernon

    49.5k13108213




    49.5k13108213











    • Please add the reference to the doc pages. There are useful remarks related to these two subjects
      – clifton_h
      1 hour ago










    • 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
      1 hour ago










    • 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
      1 hour ago










    • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
      – variable
      1 hour ago










    • "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
      1 hour ago
















    • Please add the reference to the doc pages. There are useful remarks related to these two subjects
      – clifton_h
      1 hour ago










    • 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
      1 hour ago










    • 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
      1 hour ago










    • So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
      – variable
      1 hour ago










    • "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
      1 hour ago















    Please add the reference to the doc pages. There are useful remarks related to these two subjects
    – clifton_h
    1 hour ago




    Please add the reference to the doc pages. There are useful remarks related to these two subjects
    – clifton_h
    1 hour ago












    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
    1 hour ago




    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
    1 hour ago












    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
    1 hour ago




    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
    1 hour ago












    So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
    – variable
    1 hour ago




    So if the tape were a disk drive, then would it eradicate entire disk? I thought media set meant just the .bak file...
    – variable
    1 hour ago












    "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
    1 hour ago




    "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
    1 hour ago












    up vote
    0
    down vote













    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 :-)






    share|improve this answer
























      up vote
      0
      down vote













      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 :-)






      share|improve this answer






















        up vote
        0
        down vote










        up vote
        0
        down vote









        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 :-)






        share|improve this answer












        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 :-)







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 21 mins ago









        Kin

        51.6k478185




        51.6k478185




















            variable is a new contributor. Be nice, and check out our Code of Conduct.









             

            draft saved


            draft discarded


















            variable is a new contributor. Be nice, and check out our Code of Conduct.












            variable is a new contributor. Be nice, and check out our Code of Conduct.











            variable is a new contributor. Be nice, and check out our Code of Conduct.













             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219960%2fdatabase-backup-use-case-of-format-and-init%23new-answer', 'question_page');

            );

            Post as a guest













































































            Comments

            Popular posts from this blog

            What does second last employer means? [closed]

            Installing NextGIS Connect into QGIS 3?

            One-line joke