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

by

Troubleshooting Guide: SQL Server Error 1822 – “The Database Must Be Online to Have a Database Snapshot”

Why This Error Happens

This error occurs when an attempt is made to create, access, or query a database snapshot while its source database is offline, in a restoring state, or otherwise unavailable. SQL Server requires the source database to be fully online because snapshots rely on the sparse file mechanism, which reads unmodified data pages directly from the live source database. If the source database is detached, offline, suspect, or in single-user mode while another process holds the connection, the snapshot operation fails immediately with Error 1822.

Step-by-Step Resolution Guide

Step 1: Verify the Source Database Status

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to check the state of the source database:
  SELECT name, state_desc FROM sys.databases WHERE name = 'YourSourceDatabase';
  • Confirm the state_desc column returns ONLINE. If it shows OFFLINE, RESTORING, or SUSPECT, proceed to Step 2.

Step 2: Bring the Source Database Back Online

  • If the database is OFFLINE, execute:
  ALTER DATABASE YourSourceDatabase SET ONLINE;
  • If the database is SUSPECT or flagged with corruption, check the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG (path may vary based on instance name) for underlying I/O or corruption errors before forcing it online.
  • For databases stuck in RESTORING, ensure any pending differential or log backups are applied, then run RESTORE DATABASE YourSourceDatabase WITH RECOVERY;.

Step 3: Check for File System or Permission Conflicts

  • Navigate to the snapshot file directory (commonly alongside the source .mdf path) and verify the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read/Write permissions on the folder.
  • On Windows Server 2025 / Windows 11, right-click the folder → Properties → Security tab → confirm the service account is listed with appropriate access.
  • If permissions are missing, add the account via Edit → Add → type the service account name → grant Modify rights.

Step 4: Recreate the Database Snapshot

  • Once the source database is confirmed ONLINE, drop any orphaned snapshot references:
  DROP DATABASE YourSnapshotName;
  • Create a fresh snapshot pointing to a valid sparse file location:
  CREATE DATABASE YourSnapshotName ON
  ( NAME = YourSourceDatabase_Data, FILENAME = 'D:\SQLSnapshots\YourSnapshotName.ss' )
  AS SNAPSHOT OF YourSourceDatabase;

Alternative or Verification Steps

  • Verify the fix by querying sys.databases again to confirm both the source database and the new snapshot show state_desc = ONLINE.
  • If the error persists, check Windows Server 2025 Event Viewer under Applications and Services Logs > MSSQLSERVER for Event ID 1822 details, and ensure no antivirus or backup software is locking the source .mdf/.ndf files during snapshot creation.