Quick Scan Report – Backups On the Same Drive As Your Database

What this check looks for

The drive letter of recent backup devices from msdb.dbo.backupmediafamily, compared against the drive letter of the database’s own files in sys.master_files. Where they match, the database is reported.

Two exclusions keep it honest: devices named NUL, which are the backup-to-nothing case and have their own check, and snapshot backups, which are not files in this sense.

Why it matters

The point of a backup is to survive the thing that destroys the database. A backup on the same drive does not.

The failures this leaves you exposed to are the ordinary ones:

• The drive fails. RAID reduces the odds; it does not remove them, and a controller failure or an array rebuild that goes wrong takes the whole volume.
• The volume fills. Which is more likely precisely because the backups are on it, since they are usually the fastest growing thing there.
• Ransomware. Encryption walks the file system. A .bak file sitting next to the .mdf it protects is encrypted in the same pass, and backup files are a deliberate target.
• Somebody deletes the wrong folder. Mundane and common.

In each case the database and every copy of it are lost together, which is the one outcome the backup existed to prevent.

There is a performance argument as well, and it is real but secondary. A backup reads the whole database and writes it again. When both ends of that are the same spindles or the same LUN, the backup competes with itself and with everything else the database is doing, so it takes longer and it hurts more while it runs.

The usual cause is not a decision. It is a server built with one large data volume, a backup path typed in as a default during a wizard, and nobody revisiting it.

How to confirm it yourself

;WITH backups AS (
    SELECT TOP (2000)
           bs.[database_name],
           LEFT(LOWER(bmf.[physical_device_name]), 1) AS [backup_drive],
           bmf.[physical_device_name],
           bs.[backup_finish_date]
      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] <> 'NUL'
       AND bs.[is_snapshot] = 0
     ORDER BY bs.[backup_set_id] DESC
),
files AS (
    SELECT DISTINCT
           DB_NAME([database_id])                  AS [database_name],
           LEFT(LOWER([physical_name]), 1)         AS [file_drive]
      FROM sys.master_files WITH (NOLOCK)
)
SELECT DISTINCT b.[database_name], b.[backup_drive], f.[file_drive], b.[physical_device_name]
  FROM backups AS b
 INNER JOIN files AS f
         ON f.[database_name] = b.[database_name]
        AND f.[file_drive]    = b.[backup_drive]
 ORDER BY b.[database_name];

A drive letter is a rough proxy, and it is the honest one available from inside SQL Server. Two drive letters can still be the same physical array, the same SAN LUN or the same host datastore, in which case separate letters are no protection either. That is worth confirming with whoever runs the storage: the question is not “different letter” but “different failure domain”.

How to fix it

Change where the backups are written. It costs nothing but the path.

BACKUP DATABASE [YourDatabase]
  TO DISK = N'\\backupserver\sqlbackups\YourDatabase_full.bak'
  WITH CHECKSUM, COMPRESSION, INIT, STATS = 5;

Then update the job or maintenance plan that produces them, and set the instance default so new databases inherit it:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'BackupDirectory', REG_SZ, N'\\backupserver\sqlbackups';

Where to put them, in increasing order of protection:

1. A different local volume. Better than nothing, and it survives a single disk failure. It does not survive the server, the array or ransomware.
2. A network share or a dedicated backup server. The usual answer, and it survives losing the machine.
3. Off site or immutable storage. An Azure blob container with an immutability policy, or a backup appliance with write-once retention, is the only one of these that survives ransomware with credentials.

The 3-2-1 rule is the shorthand worth knowing: three copies, on two kinds of media, one of them off site. This finding is about failing the first step of it.

If a network path is not possible, writing locally and copying immediately afterwards is a legitimate middle ground, with two conditions: the copy step has to be monitored, and the local copy has to be removed on a schedule so it does not fill the volume. Note that this arrangement is what produces the Missing Backup Files finding, so expect that one next.

How long it takes

About three hours, most of it arranging somewhere to put them and confirming the service account can write there.

Related reports

Related checks

Frequently asked questions

Our backups go to a SAN, so the drive letter is misleading. Drive letters are what SQL Server can see. The real question is whether the two share a failure domain, and that is a question for whoever runs the storage.

We copy the backups off afterwards. Then the exposure is the window between writing and copying, and whether the copy is monitored. It is a reasonable arrangement with those two things in place.

Is a different folder on the same drive enough? No. The unit of failure is the volume, not the folder.

What about backing up to a UNC path? Is that slower? It can be, and compression usually more than makes up for it since there is less to send. The protection is worth a slower backup in nearly every case.