Compare databases on different SQL Server versions

Last modified: March 28, 2025

When comparing databases on different SQL Server versions, keep in mind the following:

Compare SQL Server 2005 compatibility level 80 databases

If the compatibility level of a SQL Server 2005 database is set to 80, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Thus, comparison and synchronization may fail.

Compare SQL Server 2008 compatibility level 90 databases

If the compatibility level of a SQL Server 2008 database is set to 90, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Thus, comparison and synchronization may fail.

Compare objects unsupported on the target server

If the source database contains an object type that is not supported by the target server, that object will not appear in the comparison document during the comparison process.

Compare objects with unsupported key properties on the source server

If a source object contains key properties that are not supported by the target server, dbForge Schema Compare will identify such objects as inconsistent and display a warning - Object Table ‘table_name’ cannot be created on target server. Object definition contains options that are not supported by target server (different versions). As a result, these objects cannot be synchronized.

Compare objects with unsupported properties or subobjects on the source server

If a source object contains properties or subobjects that are not supported by the target server but can still be created or synchronized without these unsupported properties, dbForge Schema Compare will synchronize the objects and display a warning - Object ‘Object_properties_name’ cannot be created on target server. Object definition contains options that are not supported by target server (different versions). After synchronization, dbForge Schema Compare will identify the objects as equal.

Compare DDL objects with unsupported properties on the source server

If the source DDL object contains unsupported properties, such as indexes or table properties, these properties will not be displayed in the Text Compare.

Compare objects with unsupported downgraded database properties

When downgrading database properties, the synchronization succeeds. However, the properties unsupported on the source server will not be included in the generated synchronization script.

Worked example: Compare objects unsupported on the target server

Let us now illustrate how to compare objects with unsupported properties on the target server.

Suppose we want to compare and synchronize an object from the source server (MS SQL Server 2022) to the target server (MS SQL Server 2014). For the testing environment, we create sample databases: development on the source server and development2 on the target server. We also create a sample Employees table on the source server that includes the SYSTEM_VERSIONING property.

Since SYSTEM_VERSIONING is supported starting from the server version MS SQL Server 2016, dbForge Schema Compare will identify the Employees table as inconsistent and show the following warning - Object Table ‘Employees’ cannot be created on target server. Object definition contains options that are not supported by target server (different versions).

As a result, the deployment of this table to the target server will fail.

Query to create a sample table

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(50),
    Salary DECIMAL(10, 2),
    StartDate DATE NOT NULL,
    EndDate DATE NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory)
);

After initiating the schema comparison, a schema comparison document opens. As you can see, the dbForge tool detects the Employees table as inconsistent and displays a warning in the Error List. In addition, hovering over the warning icon will show the warning message.

When you try to synchronize the object to the target server and select the Open the synchronization script in the internal editor output option in the Schema Synchronization Wizard, the tool will open the generated synchronization script that will not include the script for the Employees table on the target server.

If you select the Execute the script directly against the target database output option, the synchronization fails.

In this case, the schema comparison document opens, displaying a compatibility level mismatch between the source (160) and target (120) servers that informs about unsupported options on the target server, and differences in behavior or performance due to SQL Server version variations.