Quick Scan Report – Backups to an unusual location

What this check looks for

The directory portion of physical_device_name in msdb.dbo.backupmediafamily, compared against where backups for this instance normally go. A directory that appears rarely, against a background of one or two regular destinations, is reported.

The check is skipped on Amazon RDS.

The check’s own description leads with the caveat, and it is the right one: this may be a false alarm if you run backups to various locations. An instance that genuinely writes to several destinations will report them, and that is the check working rather than failing.

Why it matters

Most of the time this is somebody taking an ad hoc backup, which is fine. The reason it is worth a look is the small number of times it is not.

The benign explanations, which cover the majority:

• A DBA took a copy before a deployment or a schema change.
• A developer refreshed a test environment.
• A one-off backup to a local drive because the usual share was unavailable.
• A new backup destination being introduced, where this finding is simply the transition.

The ones worth noticing:

• The backup destination changed and nobody updated the runbook. Now the restore procedure points at a path that has not received a backup in weeks. That is a real problem discovered at the worst time.
• A backup to a local drive that was meant to be temporary and is now the only copy, sitting on the same machine as the database. That has its own check.
• Somebody took a copy of the data. A full backup of a production database written to a workstation, a USB path or an unfamiliar share is exactly what data exfiltration looks like from inside msdb, and it is one of the few places it leaves a record.

And a specific technical hazard: an ad hoc full backup taken without COPY_ONLY resets the differential base. Your scheduled differentials from that point are differentials against somebody’s one-off copy, and restoring your own full plus your own differential fails. That is the same mechanism as the Veeam finding, caused by a person rather than a tool.

How to confirm it yourself

Where backups have been going, and how often each destination is used:

SELECT LEFT(bmf.[physical_device_name],
            LEN(bmf.[physical_device_name]) -
            CHARINDEX('\', REVERSE(bmf.[physical_device_name])))       AS [directory],
       COUNT(*)                                                        AS [backups],
       MIN(bs.[backup_start_date])                                     AS [first_seen],
       MAX(bs.[backup_start_date])                                     AS [last_seen]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bmf.[media_set_id] = bs.[media_set_id]
 WHERE bs.[backup_start_date] > DATEADD(DAY, -90, GETDATE())
   AND bmf.[physical_device_name] NOT LIKE '{%'
 GROUP BY LEFT(bmf.[physical_device_name],
               LEN(bmf.[physical_device_name]) -
               CHARINDEX('\', REVERSE(bmf.[physical_device_name])))
 ORDER BY [backups];

Read it from the bottom. The destinations with one or two backups are the unusual ones, and the ones with hundreds are your schedule.

Then look at the individual backups to that destination, which is where the answer usually is:

SELECT bs.[database_name],
       CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS [type],
       bs.[is_copy_only],
       bs.[backup_start_date],
       bs.[user_name],
       bs.[machine_name],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bmf.[media_set_id] = bs.[media_set_id]
 WHERE bmf.[physical_device_name] LIKE N'%YourUnusualPath%'
 ORDER BY bs.[backup_start_date] DESC;

user_name is the column that answers the question. It records who ran the backup, and machine_name records where from. Together they turn “an unusual location” into “Dave, from his laptop, on Tuesday”.

And check the differential base, which is the technical consequence:

SELECT [database_name], [is_copy_only], [backup_start_date], [differential_base_guid]
  FROM msdb.dbo.backupset WITH (NOLOCK)
 WHERE [type] = 'D' AND [database_name] = N'YourDatabase'
 ORDER BY [backup_start_date] DESC;

How to fix it

Identify it first. Most of the time you will stop there.

1. Read user_name and machine_name from the query above. If it is a colleague and a known reason, the finding is closed.
2. Check is_copy_only. If it is 0 on a full backup, the differential chain moved. Take a fresh scheduled full backup so your own chain has a base you control.
3. If the destination has become the new normal, update the runbook and the monitoring so the documented restore path matches reality.
4. If nobody recognizes it, treat it as a security question. A full backup of a production database is a full copy of the data, and msdb is one of the few places that fact is recorded.

Then reduce the need for ad hoc backups, which is what removes the noise:

• Use COPY_ONLY for anything taken outside the schedule, so it cannot disturb the differential chain:

BACKUP DATABASE [YourDatabase]
  TO DISK = N'\\share\adhoc\YourDatabase_predeploy.bak'
  WITH COPY_ONLY, CHECKSUM, COMPRESSION;

• Give people a sanctioned location for pre-deployment and refresh copies, so they do not invent one.
• Consider auditing BACKUP DATABASE if the security angle matters on this instance. A server audit specification records who backed up what and when, independently of msdb.

How long it takes

About half an hour to identify it, which for most findings is the whole job.

Related reports

Related checks

Frequently asked questions

We back up to several locations by design. Then this reports them, and that is expected. The check cannot know which of your destinations are sanctioned.

Does an ad hoc backup break anything? A full backup without COPY_ONLY resets the differential base, which breaks your differential chain. Log backups without COPY_ONLY truncate the log. COPY_ONLY avoids both.

How do I find out who took it? user_name and machine_name in msdb.dbo.backupset, shown in the second query above.

Should I delete the backup? Not before you know what it was for, and if the security angle is live, deleting it removes evidence. Note it, find out, then decide.