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

by

Troubleshooting Guide: SQL Server Error 9231

Why This Error Happens

Error 9231 occurs when SQL Server’s parser encounters a malformed option string, typically within a WITH clause, extended properties definition, or a backup/restore command, where an equal sign (=) was expected but not found. This is most commonly caused by a syntax error in a Transact-SQL script—such as a missing =, a misplaced comma, or an unquoted string containing special characters—or by corruption in a scripted job, maintenance plan, or SSIS package that generates T-SQL dynamically. In some cases, this error surfaces after an in-place upgrade to SQL Server 2025 when legacy scripts use deprecated or reformatted option syntax that the newer parser no longer accepts.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending T-SQL Statement

  • Open SQL Server Management Studio (SSMS) and check the Messages tab for the exact line number and batch referenced alongside Error 9231.
  • If the error originates from a SQL Agent Job, navigate to SQL Server Agent > Jobs > [Job Name] > Steps and review the Command field for the failing step.
  • Copy the full statement into a new query window for isolated testing.

Step 2: Audit the Syntax for Missing or Malformed Operators

  • Inspect WITH (...) clauses (common in BACKUP, RESTORE, CREATE INDEX, or ALTER DATABASE statements) for options missing their = sign, e.g., WITH (COMPRESSION, STATS 10) instead of the correct WITH (COMPRESSION, STATS = 10).
  • Check for stray or smart quotes (curly quotes ” “ instead of straight quotes " ") that may have been introduced by copying script text from Word documents or PDF files.
  • Verify that multi-value options are comma-separated correctly and that no reserved keyword is used as an unquoted identifier.

Step 3: Regenerate or Repair the Script Source

  • If the script originates from a Maintenance Plan, open SQL Server Management Studio > Management > Maintenance Plans, and regenerate the plan via the GUI rather than manually editing the underlying XML/T-SQL.
  • For SSIS packages, open the package in Visual Studio/SSDT, locate the Execute SQL Task, and rebuild the parameterized query using the native query builder to avoid manual string concatenation errors.
  • Save the corrected script and redeploy it to the SQL Server 2025 instance.

Step 4: Validate Compatibility Level Settings

  • Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database compatibility level.
  • If recently upgraded from an older SQL Server version, ensure any legacy syntax (such as old-style SET options) has been updated to match SQL Server 2025 standards, as some older abbreviated syntax is no longer tolerated by the parser.

Alternative or Verification Steps

  • Re-run the corrected query or job step manually in SSMS; a successful execution without Error 9231 confirms the fix.
  • If the error persists, use SQL Server Profiler or Extended Events to capture the exact batch text sent to the server at runtime, as dynamically generated SQL (via sp_executesql or application code) may differ from the static script you reviewed.