July 8, 2013 at 1:13 am
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?
July 8, 2013 at 2:00 am
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.
Joie Andrew
"Since 1982"
July 8, 2013 at 2:03 am
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
July 8, 2013 at 2:04 am
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
July 8, 2013 at 5:24 am
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.
July 8, 2013 at 5:36 am
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.
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.
July 8, 2013 at 6:25 am
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
July 13, 2013 at 8:01 am
Hi Gail Shaw..
Thank you for clarifying all my doubts.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply