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

by

Troubleshooting Guide: Error 1821 – Cannot Create a Database Snapshot on Another Database Snapshot

Why This Error Happens

Error 1821 occurs because SQL Server 2025 does not permit database snapshots to be created from a source database that is itself a snapshot. This restriction exists because snapshots rely on sparse files and a NTFS-based copy-on-write mechanism tied directly to the original source database, meaning a snapshot has no independent transactional base to generate another snapshot from. This is typically a logical/configuration error in the T-SQL script rather than a permissions or corruption issue, often caused by mistakenly referencing a snapshot name instead of the live source database.

Step-by-Step Resolution Guide

Step 1: Identify the Source Database Type

  • Run a query against sys.databases to check the source_database_id column; if this value is populated (not NULL) for your target database, it confirms the database is a snapshot.
  • Use the following command to list all snapshots and their parent sources:
  SELECT name, source_database_id, database_id 
  FROM sys.databases 
  WHERE source_database_id IS NOT NULL;

Step 2: Correct the CREATE DATABASE Script

  • Open the T-SQL script that triggered the error and locate the CREATE DATABASE... AS SNAPSHOT OF statement.
  • Change the source reference from the snapshot name (e.g., AdventureWorks_Snapshot_0800) to the actual live production database name (e.g., AdventureWorks).
  • Ensure the file path for the new snapshot does not conflict with existing .ss sparse files in the C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ directory.

Step 3: Drop Redundant Snapshot Chains

  • If the goal was to preserve a specific point-in-time state from an existing snapshot, you cannot chain it; instead, you must drop the intermediate snapshot using DROP DATABASE [SnapshotName].
  • Create a new snapshot directly from the original source database using the standard syntax:
  CREATE DATABASE AdventureWorks_Snapshot_New ON 
  ( NAME = AdventureWorks_Data, FILENAME = 
  'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\AW_New.ss' )
  AS SNAPSHOT OF AdventureWorks;

Alternative or Verification Steps

  • Verification: After re-running the corrected script, check SQL Server Management Studio (SSMS) > Object Explorer > Databases > Database Snapshots to confirm the new snapshot appears and is mounted correctly without error.
  • Alternative: If you require multiple “versions” of data history, do not use snapshot chaining; instead, schedule a SQL Agent Job to create a new snapshot directly from the primary database at different time intervals (e.g., hourly), and retain the old ones separately until they are manually dropped.