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

by

Troubleshooting Guide: SQL Server Error 4991 – NOT FOR REPLICATION Attribute Error

Why This Error Happens

This error occurs because the NOT FOR REPLICATION clause is a special attribute that SQL Server only permits on IDENTITY columns and certain constraints (like FOREIGN KEY or CHECK), not on standard data columns. The root cause is typically a migration script, an ORM-generated schema, or a manually written ALTER TABLE statement that incorrectly attempts to apply this flag to a regular integer, varchar, or other non-identity column. Essentially, the SQL engine is rejecting the syntax because the target column lacks the underlying identity property required to support replication-filtering logic.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Script

  • Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance and locate the exact ALTER TABLE statement referenced in the Event ID 4991 log.
  • Run the following query to check the column’s properties and confirm it is not currently set as an identity column:
SELECT name, is_identity FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');
  • Verify if is_identity returns 0 for the column mentioned in the error.

Step 2: Correct the Schema Design

  • If the column was intended to be an auto-incrementing key, you cannot simply add NOT FOR REPLICATION to an existing column; you must recreate the column with the IDENTITY property first.
  • Since you cannot alter an existing column to add the IDENTITY property directly, create a new column with the correct definition:
ALTER TABLE YourTableName ADD NewIDColumn INT IDENTITY(1,1) NOT FOR REPLICATION;
  • Migrate data from the old column to the new one if required, then drop the old column and rename the new one using sp_rename.

Step 3: Fix the Constraint Syntax (If Applicable)

  • If the error relates to a FOREIGN KEY or CHECK constraint rather than a column, ensure the NOT FOR REPLICATION syntax is placed correctly within the constraint definition and not attached to the column name itself.
  • Example of correct constraint syntax:
ALTER TABLE YourTableName 
ADD CONSTRAINT FK_Constraint FOREIGN KEY (ColumnName) 
REFERENCES OtherTable(ID) NOT FOR REPLICATION;
  • Re-run the script to ensure the engine parses the attribute against the constraint logic rather than the raw column type.

Step 4: Update Replication Scripts

  • If this error surfaced during a Transactional Replication setup, check the “Article Properties” in the Publication settings to ensure the schema option for “Copy NOT FOR REPLICATION” isn’t being forced on non-identity columns during snapshot generation.
  • Regenerate the snapshot after fixing the schema to ensure the Distribution Agent picks up the corrected table definition.

Alternative or Verification Steps

  • Verification: Re-run the original ALTER TABLE script. If successful, query sys.identity_columns to confirm the is_not_for_replication flag is now set to 1 for the specific column.
  • Alternative: If modifying the production schema is too risky, use a CHECK constraint with NOT FOR REPLICATION as a workaround to bypass data validation during replication without altering the column’s core identity properties.