SQL Server 2008 Auto Generate Change Scripts Legacy Problem

Question

We enable "Tools ==> Options ==> Designers ==> Table and Database Designers ==> Auto Generate Change Scripts" in our SQL Server Management Studio (SSMS). When changing our database schema, we save the script and, thanks to DB migration tools we've got installed on all the machines running our applications, we can synchronize the schema during the next software version update.

We recently switched our development copies and some production servers to SQL Server 2008. However, we still do have a few dozen SQL Server 2005 running our software in the wild. We're not planning on upgrading these for a little while.

The problem is simple. The scripts generated by SSMS simply don't always work with SQL Server 2005. SMSS is adding extra metadata to the commands (concerning lock escalation, for example). To make our scripts with 2005, we have to manually remove the extra information, otherwise the scripts don't run.

Is there any way to configure SSMS to generate scripts that are compatible with SQL Server 2005? Are there tools around that would get rid of the extra SQL automatically or at least let us know which files are problematic?

Answer 1

I've not used the 2008 management studio yet but in every previous version you have been able to set the scripting to a specific version of SQL Server in the options somewhere.

I have the 2005 management studio set to generate 2000 compatible scripts for similar reasons.

Answer 2

In SQL Server management studio 2008 goto Tools -> Options -> SQL Server Object Explorer -> Scripting and Select "Script for server version" and change it to SQL Server 2005.

Answer 3

The recommended solution to change the options appears to be corerct however it still generates ALTER TABLE SET (LOCK_ESCALATION = TABLE) on my database (I even put in compatibility mode 90)