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

by

Troubleshooting Guide: SQL Server Error 5127

Why This Error Happens

Error 5127 occurs when a CREATE DATABASE ... AS SNAPSHOT OF statement does not include an entry for every data file (NDF/MDF) that belongs to the source database. SQL Server requires a one-to-one mapping between the source database’s physical files and the sparse files defined in the snapshot command, so if the source database has had files added (or a file is simply omitted by mistake) after the snapshot script was written, the engine cannot create the copy-on-write sparse file structure correctly. This is almost always a syntax/scripting oversight rather than a permissions or corruption issue, though stale scripts following a schema change are a common trigger.

Step-by-Step Resolution Guide

Step 1: Identify All Current Data Files in the Source Database

  • Connect to the source database using SQL Server Management Studio (SSMS) or sqlcmd.
  • Run the following query to pull an accurate, current list of logical names and physical paths:
SELECT name AS LogicalName, physical_name AS PhysicalPath, type_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourSourceDB') AND type_desc = 'ROWS';
  • Note: Only data files (ROWS) are needed; log files (LOG) are never included in a snapshot definition.

Step 2: Rebuild the CREATE DATABASE Snapshot Script

  • Open your existing snapshot T-SQL script located in your deployment folder (e.g., C:\Scripts\CreateSnapshot.sql).
  • Ensure every logical name retrieved in Step 1 has a corresponding NAME = and FILENAME = entry pointing to a .ss file.
  • Example corrected syntax:
CREATE DATABASE YourSourceDB_Snapshot ON
(
    NAME = YourSourceDB_Data,
    FILENAME = 'C:\SQLSnapshots\YourSourceDB_Data.ss'
),
(
    NAME = YourSourceDB_Data2,
    FILENAME = 'C:\SQLSnapshots\YourSourceDB_Data2.ss'
)
AS SNAPSHOT OF YourSourceDB;
  • Double-check the folder C:\SQLSnapshots\ exists on the Windows Server 2025 host and that the SQL Server service account has write permissions to it.

Step 3: Account for Recently Added Files (Post-Deployment Changes)

  • If a DBA added a new NDF file to the source database after the original snapshot script was created, that file will be missing from older automation scripts.
  • Update any scheduled jobs (SQL Server Agent jobs) or PowerShell deployment scripts that generate snapshots to dynamically pull file lists from sys.master_files rather than using a hardcoded list.

Step 4: Re-execute the Snapshot Creation

  • Run the corrected script in a new query window in SSMS.
  • Confirm there are no typos in logical file names, as these are case-sensitive matches against sys.master_files.

Alternative or Verification Steps

  • Verify success by querying sys.databases and sys.master_files for the new snapshot database; the source_database_id column should match your source database’s ID.
  • If the error persists, generate the full script automatically using PowerShell with dbatools (New-DbaDbSnapshot cmdlet), which dynamically enumerates all data files and eliminates manual scripting errors entirely.