SQL Server with Mr. Denny

Aug 17 2009   11:33AM GMT

Learn to use the COPY_ONLY flag for one off backups



Posted by: Denny Cherry
Tags:
Backup & recovery
BACKUP DATABASE

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.

Denny

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • RajeshRamadas
    The actual option that you need to use for this is called "COPY_ONLY". EXAMPLE: BACKUP DATABASE dbname TO DISK='c:tempdbname.bak' WITH COPY_ONLY;
    30 pointsBadges:
    report
  • Denny Cherry
    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. Thanks, Denny
    65,490 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: