Dev and production databases have drifted apart over a year of hotfixes and I need to know exactly how before the next deployment.
What are the real options for a structure comparison, ideally producing a sync script?
Dev and production databases have drifted apart over a year of hotfixes and I need to know exactly how before the next deployment.
What are the real options for a structure comparison, ideally producing a sync script?
The free professional answer ships from Microsoft: SQL Server Data Tools inside Visual Studio carries a Schema Compare that does exactly this, and the free Community edition includes it.
In Visual Studio choose Tools, SQL Server, New Schema Comparison, set dev as source and production as target, run the compare. The result is a categorized diff, tables, columns, indexes, procedures, permissions, each difference expandable to the exact definition change, with checkboxes selecting what to include. The Generate Script button then produces the sync script you asked for, which you review and run yourself rather than letting the tool touch production directly, the correct paranoia for a drifted prod.
The query route for quick spot checks without installing anything: INFORMATION_SCHEMA views or sys.objects joined across servers via a linked server or exported to temp tables. A join of INFORMATION_SCHEMA.COLUMNS on table and column name with a full outer join surfaces missing columns and type mismatches in one query. Honest scope: this catches tables and columns well, but procedures, indexes and constraints get painful fast, which is where Schema Compare earns its place.
One discipline note from the trenches: after this comparison closes the drift, the cure for it reopening is keeping schema in source control, SSDT database projects or migration scripts, so the next hotfix lands in dev and prod through the same file. The compare tool then becomes a verification step rather than an archaeology expedition.
Schema Compare found 40 differences including two prod hotfix indexes nobody documented. Sync script reviewed and staged. The source control sermon has been forwarded to the team, deservedly.