Difference between Full backup and Copy-only full backup

  • IT researcher

    SSCertifiable

    Points: 7290

    I saw in this link that full backup does not truncate the log.

    So what is the difference between full backup and copy-only full backup?

    For the log backup there is copy-only backup which prevent the log chain from breaking without truncating the log. So what is copy-only full backup?

  • Joie Andrew

    One Orange Chip

    Points: 27273

    Copy-only full backups are the same concept as copy-only log backups. It does not write an archive bit so the log chain is not interrupted with the backup. It is useful if you are doing one-off backups that you want to use but do not want to keep track of in case of a restore scenario. A good example is taking a backup of a production system in order to bring it over to a dev environment. With a copy-only backup you can backup the database and move the backup over wherever you need to without having to keep track of it in case you need to restore from that timeframe like you would with regular full/log backups.

    Copy-Only Backups

    Joie Andrew
    "Since 1982"

  • Gail Shaw

    SSC Guru

    Points: 1004474

    A copy-only full backup does not reset the differential base. That's the only difference.

    See - 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
  • Gail Shaw

    SSC Guru

    Points: 1004474

    Joie Andrew (7/8/2013)


    Copy-only full backups are the same concept as copy-only log backups. It does not write an archive bit so the log chain is not interrupted with the backup.

    Full backups never truncate the transaction log, normal or copy-only. Only transaction log backups will truncate the transaction log.

    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
  • IT researcher

    SSCertifiable

    Points: 7290

    I am not using differential backup in my environment. i use full backup and transaction log backup.So in this case taking full backup and copy only full backup are same? Taking only full backup will not affect log chain i guess.

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    Joie Andrew (7/8/2013)


    Copy-only full backups are the same concept as copy-only log backups. It does not write an archive bit so the log chain is not interrupted with the backup. It is useful if you are doing one-off backups that you want to use but do not want to keep track of in case of a restore scenario. A good example is taking a backup of a production system in order to bring it over to a dev environment. With a copy-only backup you can backup the database and move the backup over wherever you need to without having to keep track of it in case you need to restore from that timeframe like you would with regular full/log backups.

    Copy-Only Backups

    Thanks Gail, good article. Quick question, even if its a copy_only backup, it would still get recorded in the backup history right? Or it will act as if it never happened?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    IT researcher (7/8/2013)


    I am not using differential backup in my environment. i use full backup and transaction log backup.So in this case taking full backup and copy only full backup are same? Taking only full backup will not affect log chain i guess.

    Correct. Taking full backups does not and has never broken the log chain.

    Other than the 'not resetting differential base' a copy only full backup is exactly the same as a normal full backup in every way.

    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
  • IT researcher

    SSCertifiable

    Points: 7290

    Hi Gail Shaw..

    Thank you for clarifying all my doubts.

Viewing 8 posts - 1 through 8 (of 8 total)

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