We’re currently look into upgrading from SQL Server 2008 R2 to SQL Server 2012. I think the plan is to detach the existing databases, install 2012 and attach to the new instance. Once we’ve attached we will set the compatibility level in the databases properties to be SQL Server 2012.
Our databases are under source control in TFS using SSDT for VS2012. We have several enivornments that we will need to upgrade and to make sure we don’t forget to update the compatibility level we will update it in our SSDT projects and then it will get updated when we publish. To set the compatibility level in SSDT click on the “Database Settings…” button in the “Project Settings” tab of the database properties. Then under “Miscellaneous” select the required value from the “Compatibility Level” drop down.
However, by default SSDT doesn’t check for differences in the compatibility level when it publishes a database project. You can enable it in the “Advanced” settings of the “Publish Wizard” and then save the publish profile for later use.
Then if there is a difference between the compatibility level in your database and project a statement similar to the following will be included in the publish script.