Quick Scan Report – Backup To NUL

What this check looks for

Any backup taken in the last three days whose physical_device_name in msdb.dbo.backupmediafamily is NUL. Up to 100 are reported, ordered by backup type, and each message names the database, the type (Full, Differential or Log), whether it was COPY_ONLY, and when it ran.

Why it matters

NUL is the Windows bit bucket. A backup written to it is deleted as it is written.

That would be a curiosity if it left no trace, but it does not. SQL Server records the backup in msdb exactly as if it had succeeded, so:

  • The job succeeds and nobody is alerted.
  • msdb.dbo.backupset grows a row saying this database was backed up at this time.
  • Every report that reads backup history, including the ones in this product, says the database is backed up.
  • The log chain is affected. A log backup to NUL truncates the transaction log and then discards the only copy of those records. The chain is broken and no point in time restore past that moment is possible, and the first anyone knows about it is during a restore.

A full backup to NUL is bad. A log backup to NUL is worse, because it quietly destroys the recoverability of every log backup taken before it.

The usual sources are a script written to test backup throughput without consuming disk, a copy and paste from an article demonstrating compression ratios, and a maintenance job where somebody changed the destination to NUL to get past a full drive and did not change it back.

How to confirm it yourself

SELECT TOP (100)
       bs.[database_name],
       CASE bs.[type] WHEN 'D' THEN 'Full'
                      WHEN 'I' THEN 'Differential'
                      WHEN 'L' THEN 'Log'
                      ELSE bs.[type] END          AS [backup_type],
       bs.[is_copy_only],
       bs.[backup_start_date],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bs.[media_set_id] = bmf.[media_set_id]
 WHERE bmf.[physical_device_name] = 'NUL'
 ORDER BY bs.[backup_start_date] DESC;

Drop the three day window the check uses and this shows how long it has been going on.

How to fix it

Find what issued it. The backup history says which database and when, so match that against your Agent job schedules:

SELECT j.[name] AS [job_name], s.[step_id], s.[step_name], s.[command]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobsteps AS s WITH (NOLOCK)
         ON s.[job_id] = j.[job_id]
 WHERE s.[command] LIKE '%NUL%'
 ORDER BY j.[name], s.[step_id];

Also check maintenance plans, any third party backup tool’s configuration, and ad hoc scripts on the server.

Then repair the damage, not just the job. Point the backup at real storage, and immediately:

  1. Take a fresh full backup of every affected database, to a real file. This starts a new log chain.
  2. Take a log backup straight afterwards if the database is in full or bulk logged recovery.
  3. Verify you can restore it somewhere. A backup nobody has restored is a hope.

Do not assume the differential chain survived. A full backup to NUL resets the differential base to a backup that does not exist.

How long it takes

About two hours, mostly the fresh full backups. Finding the job that did it takes minutes.

Report Why you would go there
Backup Status Whether every database actually has a usable recent backup.
Backup Ledger The full backup history for a database, including where each one went.
Recovery Exposure What a restore would cost right now, given what really exists.
Restore History Whether any of these backups has ever been restored.
Job Commands The text of every Agent job step, which is where NUL will be.
Check
Backups to an unusual location The same problem one step less extreme.
Full or bulk logged recovery model with no backups A log chain that was never started.
Missing backup files Backup history that points at files no longer on disk.

Frequently asked questions

Is a COPY_ONLY backup to NUL harmless? Less harmful, because a copy only backup does not reset the differential base and a copy only log backup does not truncate the log. It still writes a backup history row that makes the database look protected. The check reports it and says (COPY_ONLY) so you can tell the difference.

Why only the last three days? Because this is a scan of the instance’s current state, not an audit. Run the query above without the date filter to see the whole history.

Someone did this once to measure backup speed. Is that fine? It is a legitimate technique, and it is why COPY_ONLY exists. Use BACKUP DATABASE ... TO DISK = 'NUL' WITH COPY_ONLY if you have to do it at all, and never for a log backup.

The job was fixed months ago and this still fires. Then it ran again in the last three days. The check reads current backup history, not configuration.