Backups

  • Lokesh Vij

    SSChampion

    Points: 10836

    demonfox (9/17/2012)


    forgot Cell phone !! That is more of amber than blue ;D

    btw, the link you provided were really informative.

    Thanks for that...

    You are welcome Avi:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Paul White

    SSC Guru

    Points: 150442

    Divine Flame (9/17/2012)


    SQL Kiwi (9/17/2012)


    Good question, I guess, though I would have preferred it without the INIT trap.

    Thanks for the feedback Paul. Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

    It did. My personal preference (which no-one else may share, and that is fine) is for QotD questions to test one specific piece of knowledge. Not being able to combine compressed and non-compressed backups in the same set is one such piece of knowledge; knowing the difference between INIT and FORMAT is another. I might have written this as two questions, but again, this is just an opinion, nothing more than that.

  • SQLmountain

    SSCommitted

    Points: 1505

    Divine Flame (9/17/2012)


    SQL Kiwi (9/17/2012)


    Good question, I guess, though I would have preferred it without the INIT trap.

    Thanks for the feedback Paul. Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

    That it did. Nice.

  • Thomas Abraham

    SSChampion

    Points: 10761

    palotaiarpad (9/17/2012)


    I guessed and won. I thought, SQL Server stores the compression flag in the header and not at the data part of the backup. And as the header remains, the second statement must fail. Actually i didn't find any clues what else is stored in the header.

    I knew the header was left by INIT, guess I didn't think about the compression flag being in the header. Tell me why I don't like Mondays ...

    Thanks for the question Divine!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • This was removed by the editor as SPAM

  • sestell1

    SSChampion

    Points: 10230

    Divine Flame (9/17/2012)...Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

    Thanks for the excellent question. I got it wrong and learned something important today!

  • Steve Nogradi

    Default port

    Points: 1430

    Great question! I chose the wrong answer but I learned something new.

  • Error Handler

    Ten Centuries

    Points: 1086

    Nice Question! GOt it

    Best,
    Naseer Ahmad
    SQL Server DBA

  • TomThomson

    SSC Guru

    Points: 104773

    Good question.

    But the referenced BoL page doesn't describe this behaviour, the problem isn't that a media set can't contain both compressed and uncompressed backups (if both backups worked in the script given in the question the media set would never contain more than one backup so it certainly wouldn't contain both a compressed backup and an uncompressed one, so exclusion of holding both doesn't preclude both those commands working). To account for the behaviour also requires the fact that "with INIT" doesn't reinitialise the media, just destroys any existing backup sets in it, and the result is that if a media set has ever contained a compressed backup it can never contain an uncompressed one even if the compressed one has been deleted (plus of course the same with compressed and uncompressed swapped).

    Thanks to Raghu for the reference to something that does account for this behaviour.

    So today I've learnt that INIT doesn't cause modification of the header. I guess I'll start using FORMAT everywhere I used to use INIT.

    Tom

  • Rob Schripsema

    SSCertifiable

    Points: 7469

    Divine Flame (9/17/2012)


    Carlo Romagnano (9/17/2012)


    You need add the clause "FORMAT" to succeed the second statement!

    BACKUP DATABASE TestDatabase_1

    TO DISK = 'C:\TestDatabaseBackup.BAK'

    WITH COMPRESSION

    GO

    BACKUP DATABASE TestDatabase_2

    TO DISK = 'C:\TestDatabaseBackup.BAK'

    WITH NO_COMPRESSION, INIT

    , FORMAT

    I left the "FORMAT" deliberately just to make people aware about the difference between FORMAT & INIT. Many people think of FORMAT & INIT as the same.

    Yup, that was me. I learned something today, too! Thanks for a good question.

    Rob Schripsema
    Propack, Inc.

  • Dave62

    SSCertifiable

    Points: 6703

    Thanks for the question. I got it right but first had to decide whether the "trick" to the question was the location 'C:\...' looking the same but being on 2 different servers. Same server wasn't specified and running the commands together as a batch wasn't specified. The correct answer would have been different if it wasn't the same server.

    Enjoy!

  • Jim_K

    SSCrazy

    Points: 2363

    Learned something today about the differences between Format and Init. Does the difference between the two gain you an advantage when you are backing up to tape (it's been years since any dbs I've been involved with backed up straight to tape)?

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    @Tom - Glad to be help. 🙂

    @Jim_K - I am not sure if this can be an advantage, but to my knowledge -

    Say the 1TB tape where it has backups taken each day from past 3 weeks.

    (now we know that the tape's all backups are either in the compressed or uncompressed format (not both), but lets say compressed)

    A.

    Now if used the FORMAT command, the tape's header part is formatted, so it does not has the clue what kind of backups previously exists (as the entire media will be invalid), so the new first backup can be in compressed or in uncompressed format. Here FORMAT can be used for making the existing tape usable for new set of backups (any format).

    B.

    If used the INIT command, the tape's header is not been formatted, so it holds the details of what kind of backup it consists. Here when the backup is done even without using the COMPRESSION word the backup goes as compressed and it gets overwitten to the existing media (OR it will give the error message saying the format of the existing media and the new backup request is not matching and backup might fail). The backup always makes a default check on the HEADER section on the associated media and then act accordingly.

    In the part B, if the FORMAT is used then the header details is removed and as the COMPRESS word is not mentioned so the data goes as uncompressed because the BACKUP commend could not find the details and it considers it as a new media.

    In the sense of advantage

    - By using FORMAT - I can say it just helps the tape to be reused for any type of backups completely.

    - By using INIT - The backup needs to be continued in the same way as the previous backup format were and then reuse the entire tape's space.

    Hope this helps.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Thanks for the lesson. Learned something today!

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply