Backups

  • Divine Flame

    SSCoach

    Points: 15941

    Comments posted to this topic are about the item Backups


    Sujeet Singh

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the question, was nice and good brain exercise on Sunday 🙂

    Got the answer wrong and I kind of tricked myself into it with the INIT as it indicates that "If INIT is specified, any existing backup set on that device is overwritten..." (and RETAIN and EXPIRYDATE is not mentioned, so)

    Even though I have noticed the COMPRESSION and NO_COMPRESSION, considering the INIT and it saying I thought both will succeed and but the .BAK file will contain only the 2nd database (as it is going to overwrite). If the default NOINIT was mentioned then the "Restriction" might would have apply.

    So here the INIT in the second batch has no significance at all.

    I couldn't test the code on my local as it is running on 2K8-R2 Express edition

    Msg 1844, Level 16, State 1, Line 3

    BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition with Advanced Services.

    EDIT;

    I see here that the HEADER is untouched when the INIT is used so the information is not entirely erased and it checks to see if the media is been compressed or not.http://blogs.msdn.com/b/sql_pfe_blog/archive/2010/08/09/backup-compression-behavior-when-appending-backups-to-an-existing-media-set.aspx">

    http://blogs.msdn.com/b/sql_pfe_blog/archive/2010/08/09/backup-compression-behavior-when-appending-backups-to-an-existing-media-set.aspx

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

  • demonfox

    SSCertifiable

    Points: 6289

    nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁

    I need coffee ...

    thanks for the question..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Lokesh Vij

    SSChampion

    Points: 10836

    Wonderful question Divine. I would recommend taking a look at the following link as well:

    http://msdn.microsoft.com/en-us/library/ms178062.aspx

    It talks about Media Sets, Media Families, and Backup Sets in SQL Server.

    ~ 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

  • Lokesh Vij

    SSChampion

    Points: 10836

    demonfox (9/16/2012)


    nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁

    I need coffee

    Me too got this question wrong...

    Took a wrong turn on the way and reached office half an hour late...

    Forgot to carry my cell phone...

    My my my ...Monday morning Blues....Whoopppp! :w00t:

    ~ 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

  • Ravi SQL

    SSCommitted

    Points: 1714

    Good question...

    Regards,
    Ravi.

  • okbangas

    SSChampion

    Points: 11773

    In most scenarios, I would say that both should fail as SQL Server does/should not have access to create files on the system drive, but that is a different scenario I guess. I got this wrong, because I thought init would cause an overwrite and thus allow the backup file to become uncompressed. Nice question.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

    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.

  • Paul White

    SSC Guru

    Points: 150442

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

  • demonfox

    SSCertifiable

    Points: 6289

    Lokesh Vij (9/17/2012)


    demonfox (9/16/2012)


    nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁

    I need coffee

    Me too got this question wrong...

    Took a wrong turn on the way and reached office half an hour late...

    Forgot to carry my cell phone...

    My my my ...Monday morning Blues....Whoopppp! :w00t:

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

    btw, the link you provided were really informative.

    Thanks for that...

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Carlo Romagnano

    SSC-Insane

    Points: 21795

    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

  • Divine Flame

    SSCoach

    Points: 15941

    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.


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    Lokesh Vij (9/16/2012)


    Wonderful question Divine. I would recommend taking a look at the following link as well:

    http://msdn.microsoft.com/en-us/library/ms178062.aspx

    It talks about Media Sets, Media Families, and Backup Sets in SQL Server.

    Thanks for the feedback & for providing the informative link Lokesh.


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    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.


    Sujeet Singh

  • Lokesh Vij

    SSChampion

    Points: 10836

    Thanks for the feedback & for providing the informative link Lokesh.

    You are welcome Divine 🙂

    ~ 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

Viewing 15 posts - 1 through 15 (of 32 total)

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