liveBook · Manning

COPY_ONLY

As explained in 100 SQL Server Mistakes and How to Avoid Them

COPY_ONLY is a SQL Server backup option that creates a backup without changing the differential base or affecting the normal backup sequence.

Mistake: taking a normal full backup for an ad hoc request. That backup becomes the differential base, so discarding it can make later differential backups unusable in the expected restore sequence. section 12.10

The discarded 10 a.m. backup

A 1 a.m. full backup is followed by a 6 a.m. differential and transaction log backups through 1 p.m. A discarded full backup taken at 10 a.m. changes the differential base, making the 12 p.m. differential unavailable for the standard restore sequence and wasting troubleshooting time. section 12.10

Listing 12.11 Taking a COPY_ONLY backup
BACKUP DATABASE Marketing 
TO  DISK = 'D:\Backups\MarketingFullCopyOnly.bak' 
WITH COPY_ONLY ;

Use WITH COPY_ONLY for an ad hoc full backup of the Marketing database; it avoids changing the differential base. section 12.10

Safer practice:

  • Use COPY_ONLY for backups taken outside the routine schedule, especially when differential backups are used. section 12.10
  • Use it consistently even without differentials to avoid checking the backup schedule before every ad hoc backup. section 12.10

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains the differential-base effect, demonstrates T-SQL syntax, and illustrates the resulting restore-sequence problem.
Examples
Uses a discarded 10 a.m. full backup and a Marketing database T-SQL command.
What it assumes
Assumes differential backups and restore sequences, while clarifying how COPY_ONLY protects them.
What you can do after
Operate/configure ad hoc backups with the WITH COPY_ONLY option.
How it connects
Connects ad hoc backups with differential backups, restore sequences, and transaction log backups.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.