Post reply

Does specifying COPY_ONLY make a difference?

  • December 1, 2011 at 3:33 am

    #247726

    Hi

    I'm doing some backups to be restored to a different server as new DBs and it got me thinking...

    Is there any reason, other than the one pointed out by Microsoft (that a copy-only backup cannot be used as a differential base) why I would need to use COPY_ONLY? As in my experience a perfectly valid restore from a non-COPY_ONLY backup works fine.

    Or are there other considerations / best practices?

    Link to BACKUP(T-SQL) here -> http://msdn.microsoft.com/en-us/library/ms186865.aspx

    Derek.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • December 1, 2011 at 3:51 am

    #1415120

    COPY_ONLY does not break the log chain.

    So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

  • December 1, 2011 at 4:34 am

    #1415153

    Suresh B. (12/1/2011)

    COPY_ONLY does not break the log chain.

    So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

    COPY_ONLY is useful only when you're working with differential backups.

    AFAIK, Log Shipping does not use differential backups, so COPY_ONLY has no place there.

    -- Gianluca Sartori

  • December 1, 2011 at 4:39 am

    #1415155

    I thought so. Thanks for your reply!

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • December 1, 2011 at 5:00 am

    #1415168

    Suresh B. (12/1/2011)

    COPY_ONLY does not break the log chain.

    So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

    full backups without copy_only do not break the log chain either. You can take as many full backups as you like when doing log shipping. Copy_only with full backups is only relevant to differentials

    ---------------------------------------------------------------------

  • December 1, 2011 at 7:23 am

    #1415245

    Suresh B. (12/1/2011)

    COPY_ONLY does not break the log chain.

    Full backups do not EVER break the log chain, regardless of whether they're taken with copy_only or not..

    So if you have log shipping configured, you can use COPY_ONLY option to take a full database backup of testing, etc.

    You can, but you don't have to.

    http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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