liveBook · Manning

Ad hoc backup

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

An ad hoc backup is a database backup taken outside the regularly scheduled backup plan for an immediate restore point or data refresh.

An ad hoc backup is taken outside the normal schedule, but an ordinary full backup can silently change the differential base. Discarding that base can make later differential backups unusable and waste restore time while the team diagnoses an apparently broken sequence. section 12.10

The 10 a.m. backup nobody recorded

Suppose a full backup runs at 1 a.m., a differential at 6 a.m., and transaction log backups run hourly. An unplanned full backup at 10 a.m. changes the differential base, so a later 12 p.m. differential may no longer restore from the expected chain. Use COPY_ONLY for the ad hoc backup; it preserves the differential base and restore sequence. section 12.10

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

Run a COPY_ONLY full backup for the Marketing database when an immediate backup is needed outside the planned schedule. section 12.10

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

How deep it goes
Explains differential-base mechanics, restore consequences, an operational scenario, and the corrective T-SQL option.
Examples
Uses a Marketing database command and a 1 a.m., 6 a.m., 10 a.m., and 12 p.m. backup timeline.
What it assumes
Assumes basic familiarity with full, differential, and transaction log backups while introducing their restore-chain interaction.
What you can do after
Operate/configure SQL Server ad hoc backups with the COPY_ONLY option to preserve the planned restore sequence.
How it connects
Connects ad hoc backups to differential backups, transaction log backups, restore sequences, and recovery models.
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.