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

by

Troubleshooting Guide: SQL Server Error 1812 — COLLATE Clause Not Allowed with FOR ATTACH

Why This Error Happens

This error occurs because SQL Server 2025 does not permit specifying a COLLATE clause in a CREATE DATABASE statement when the FOR ATTACH option is used, since collation settings for an attached database are already embedded in the existing MDF/LDF system catalog metadata. Attempting to override the collation at attach-time creates a conflict between the script’s declared intent and the physical file’s stored metadata. This typically happens when a DBA reuses a database creation script (originally meant for a fresh database) to attach existing data files without stripping out the COLLATE syntax.

Step-by-Step Resolution Guide

Step 1: Identify and Isolate the Offending T-SQL Script

  • Open the script that triggered Error 1812 in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Locate the CREATE DATABASE [YourDB] ... FOR ATTACH statement.
  • Check specifically for a trailing or embedded COLLATE <Collation_Name> clause within that same statement.

Step 2: Remove the COLLATE Clause from the Attach Statement

  • Edit the script to strip out the COLLATE keyword and its associated value entirely.
  • Ensure the syntax strictly follows: CREATE DATABASE [YourDB] ON (FILENAME = 'C:\Data\YourDB.mdf'), (FILENAME = 'C:\Data\YourDB_log.ldf') FOR ATTACH;
  • Save the corrected script before re-execution.

Step 3: Verify File Paths and Permissions on Windows Server 2025

  • Navigate to the physical file location (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions on the .mdf and .ldf files via Right-click > Properties > Security.
  • Re-run the corrected CREATE DATABASE ... FOR ATTACH command via SSMS.

Step 4: Adjust Collation Post-Attach (If a Different Collation Is Required)

  • If you genuinely need to change the database collation, do so after the attach succeeds, rather than during the attach statement.
  • Run the following command separately: ALTER DATABASE [YourDB] COLLATE SQL_Latin1_General_CP1_CI_AS;
  • Note that this only changes the default collation for new objects; existing column-level collations will remain unchanged unless manually altered.

Alternative or Verification Steps

  • Verify the fix: Run SELECT name, collation_name FROM sys.databases WHERE name = 'YourDB'; to confirm the database attached successfully and check its current collation setting.
  • Alternative approach: If the data files are corrupted or collation metadata is unreadable, detach the database (if partially attached) and use DBCC CHECKDB on a copy of the files in a scratch instance to rule out corruption before re-attaching to production.