August 22, 2008 at 11:56 pm
Eric is right -
Full database backups without copy_only don't break a log chain, they zero out the DCM so that differential backups can't be used with previous full database backups.
Transaction log backups without copy_only *may break* log chains (that is, if you try to skip them when you restore).
So:
Use full database backups with copy_only if you want to be able to use differential backups with your previous full db backup.
Use log backups with copy_only if you don't want this log backup as part of your log backup chain
September 9, 2008 at 4:54 am
Yes, I've seen that the copy_only backups are useful when you've implemented a backup scheme that includes differential backups. But, Steve, you made a comment in the video that this is only useful for databases in full recovery mode. I think you can set up backups as follows, for example, on a database in simple recovery mode: Full weekly and differential nightly. (Yes, I know you can only recover up to the previous night's differential.) Then the COPY_ONLY is useful to run an on-demand backup of the database anytime during that week without breaking the backup chain. So I think you can run COPY_ONLY on databases in SIMPLE mode. Can you confirm?
December 4, 2008 at 9:34 am
Sorry, I should have clarified more how this works. This was intended to show off Copy Only backups, and not cover all aspects of backups.
Full backups do not break the LSN chain. I misspoke if that's in there. I'll check and try to re-shoot this if it is. If the LSN broke, then log shipping would require full backups to move over.
However what typically happens is that the last full backup or two are all that are kept, and logs backups in between are often deleted by automated processes (maintenance plans and home grown solutions) so as to save space. Having extra backups occurring means that you might not be aware of which backups to restore with which logs. Especially if they are not in the same folders.
Or you have people writing backups to the same device or file, which means that you have a new starting point for recovery.
A full backup lowers the time it takes to recover if there is an issue. And it lowers risk. Every extra log you need to restore means that is one more file you have to ensure is intact. If you are going back to the full from two night's ago to do a restore, you need to be sure not only that you have every log file since that backup, but also that they are readable, no media errors, etc.
You also have the issue of differential backups, which are taken to speed recovery as well. The problems with a regular backup is they can invalidate a differential, which can cause the admin issues.
The more I think about it, the more I think I've misspoken in the video since I viewed it as an admin having issues with backup chains because older logs might not be available. I'll remove the video for the time being until I can clarify the workings of the backup process.
June 12, 2012 at 10:16 am
Indeed, Eric is right. Ad-hoc full backups don't affect a backup regime of Full Backups and Log Backups.
March 7, 2015 at 8:47 pm
Please, remove this video
A Copy Only backup as its name implies is a "Copy Only" and does not touch anything in the backup chain
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply