Posted by: Denny Cherry
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.