Learn to use the COPY_ONLY flag for one off backups

Published On: 2009-08-17By:

Taking a full backup when doing major database upgrades is a great idea.  However if you are taking differential backups this one off full backup will break the differential backup chain.

Assume you take full backups Sunday morning and differentials every other morning.  On Thursday you need to do take a full backup and do a database release.  On Friday you need to restore the database, and you restore Sunday’s backup and then try and restore the diff from Friday morning and get an error.

You can avoid this by using the COPY_ONLY flag when running the BACKUP DATABASE command.  This tells the SQL Server to take the full backup without resetting the internal flags so that the differential backups will still be usable.


Contact the Author | Contact DCAC

2 responses to “Learn to use the COPY_ONLY flag for one off backups”

  1. RajeshRamadas says:

    The actual option that you need to use for this is called “COPY_ONLY”.

    BACKUP DATABASE dbname TO DISK=’c:tempdbname.bak’ WITH COPY_ONLY;

  2. mrdenny says:

    Rajesh, Thanks for pointing that out. I’ve updated the title and the post. Not sure how I missed that. Sometimes the fingers type faster than the eyes can proof read.



Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link