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

by

Why This Error Happens

Error 1064 occurs when a T-SQL statement (typically CREATE DATABASE, ALTER DATABASE, or CREATE TABLE/CREATE INDEX with filestream/partitioning options) attempts to assign a LOG ON clause to a filegroup instead of directly to a physical log file definition. SQL Server strictly separates data storage (filegroups, which hold .mdf/.ndf files) from the transaction log (which uses .ldf files and has no filegroup concept), so mixing the two syntaxes triggers this parser-level validation failure. This is almost always caused by a scripting error, a migrated/legacy script from another RDBMS, or a copy-paste mistake in a DDL template.

Step-by-Step Resolution Guide

Step 1: Review the Offending T-SQL Script

  • Open the script that generated Error 1064 in SSMS (SQL Server Management Studio 21 or later, compatible with SQL Server 2025).
  • Locate the LOG ON clause and check whether it references a FILEGROUP keyword or a named filegroup (e.g., LOG ON [PRIMARY] or LOG ON [FG_LOGS]).
  • Confirm the exact line number from the error message in the Messages pane.

Step 2: Correct the Syntax to Separate Data and Log Definitions

  • Rewrite the LOG ON clause to use only (NAME = ..., FILENAME = ...) pairs, without any FILEGROUP reference.
  • Example corrected syntax:
  CREATE DATABASE SalesDB
  ON PRIMARY
  (NAME = SalesDB_Data, FILENAME = 'C:\SQLData\SalesDB.mdf')
  LOG ON
  (NAME = SalesDB_Log, FILENAME = 'C:\SQLData\SalesDB_log.ldf');
  • Ensure the FILEGROUP keyword appears only in the ON clause (for data files), never in LOG ON.

Step 3: Validate File Paths and Permissions on Windows Server 2025 / Windows 11

  • Confirm the target directory (e.g., C:\SQLData\) exists and the SQL Server service account has Write/Modify NTFS permissions via Properties > Security.
  • Check for path conflicts using PowerShell: Test-Path "C:\SQLData\SalesDB_log.ldf".
  • Re-run the corrected script in a new query window.

Step 4: Check for Legacy or Migrated Scripts

  • If the script was imported from SQL Server 2017/2019 or another platform, scan for deprecated multi-filegroup log syntax using Ctrl+Shift+F (Find in Files) in SSMS across the project folder.
  • Update any automation templates (PowerShell DSC, Azure Data Studio notebooks, or dbatools scripts) that may still reference the invalid pattern.

Alternative or Verification Steps

  • Verify the fix by running SELECT name, type_desc, physical_name FROM sys.master_files WHERE database_id = DB_ID('SalesDB'); — the log file should show type_desc = 'LOG' with no filegroup association.
  • If the error persists on ALTER DATABASE, drop and recreate the log file definition using ALTER DATABASE ... REMOVE FILE followed by ALTER DATABASE ... ADD LOG FILE instead of modifying it in place.