How to fix Error 3002 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 3002 — Cannot BACKUP or RESTORE a Database Snapshot

Why This Error Happens

Error 3002 occurs because SQL Server database snapshots are designed as read-only, point-in-time views that rely on sparse files and the live source database to resolve data pages. Since a snapshot has no independent transaction log and depends entirely on the source database for consistency, SQL Server explicitly blocks BACKUP and RESTORE operations against it. This is almost always a result of an administrator or script mistakenly targeting a snapshot name instead of the actual source database.

Step-by-Step Resolution Guide

Step 1: Confirm Whether the Target Is Actually a Snapshot

  • Run the following query to list all databases and identify snapshots:
SELECT name, source_database_id, create_date 
FROM sys.databases 
WHERE source_database_id IS NOT NULL;
  • If source_database_id is not NULL, the database is a snapshot and cannot be backed up or restored directly.
  • Cross-check the name against your backup job or script (e.g., AdventureWorks_Snapshot_0800 vs. AdventureWorks).

Step 2: Redirect Backup/Restore Operations to the Source Database

  • Update your backup script, SQL Agent Job, or maintenance plan to reference the actual source database name, not the snapshot.
  • Example corrected command:
BACKUP DATABASE [AdventureWorks] 
TO DISK = N'D:\SQLBackups\AdventureWorks.bak' 
WITH COMPRESSION, STATS = 10;
  • In SQL Server Management Studio (SSMS) 21, verify under Databases > Database Snapshots that you are not right-clicking a snapshot node to initiate backup.

Step 3: Check for Orphaned or Stale Snapshots Consuming Resources

  • Stale snapshots can cause confusion in scripts or automated jobs that enumerate databases dynamically.
  • List snapshot sparse files on disk to confirm locations:
SELECT name, physical_name 
FROM sys.master_files 
WHERE database_id IN (
    SELECT database_id FROM sys.databases WHERE source_database_id IS NOT NULL
);
  • If snapshots are no longer needed (e.g., leftover from a reporting or rollback test), drop them to avoid future job failures:
DROP DATABASE [AdventureWorks_Snapshot_0800];

Step 4: Audit Automated Jobs and Maintenance Plans

  • Open SQL Server Agent > Jobs in SSMS and review any job step that performs BACKUP DATABASE using dynamic SQL or a loop (e.g., sp_MSforeachdb).
  • Ensure the loop explicitly excludes snapshot databases:
WHERE source_database_id IS NULL
  • Save and re-run the job to confirm it no longer targets snapshot names.

Alternative or Verification Steps

  • Verify the fix by manually running the corrected BACKUP DATABASE command against the source database and confirming it completes with “Processed… pages” and no Error 3002.
  • If you need point-in-time recovery capability similar to a snapshot, use native Full + Transaction Log backups on the source database instead of relying on database snapshots, since snapshots are not a substitute for disaster recovery backups.