Thursday, July 10, 2008
Backups with COPY_ONLY option
We have heard a lot that having regular backups is a must in any database environment. But, there are cases where having even regular full, differential and log backups will not ensure that the database is secure, and you can lose the work for hours or days. This happens due to backup chain break when someone takes a full backup apart from the regular scheduled backups and the subsequent differential backups will have a different backup_lsn. The remedy is using the COPY_ONLY option with full backups which doesn’t break the chain. Paul Randal has illustrated the importance of this with a post which I noticed recently only http://www.sqlskills.com/blogs/paul/2007/10/15/BACKUPWITHCOPYONLYHowToAvoidBreakingTheBackupChain.aspx. So as a DBA, next time when your developers ask you for a backupset of a production database, either restore it from a regular backupset or take a backup with the COPY_ONLY option.