Setting SQL Server Database Compatibility level for all databases

Setting the database compatibility level for a couple of SQL Server databases can be a simple task.  But what if you had 30 or 40 databases?  For those of you with a lot of databases, he is a simple step by step process to update the compatibility level to version 11 (SQL Server 2012).

  1. In SQL Server Studio Manager set the query output to text
  2. Create a new query window
  3. Excute the following script
    select ‘ALTER DATABASE ‘ + rtrim(INTERID) + ‘ SET COMPATIBILITY_LEVEL = 110’ from DYNAMICS..SY01500
    union
    select ‘ALTER DATABASE ‘ + ‘DYNAMICS’ + ‘ SET COMPATIBILITY_LEVEL = 110’
  4. Copy the results and paste into a new SQL Query Window
  5. Execute the new query window.
  6. All of the GP Databases should be updated to the new compatibility level.

If you are updaing to SQL Server 2008 change 110 to 100.

Share this post

Related Posts

Webinar Recap: CMMC News for the DIB

The DoD announcement on Nov 4 “Strategic Direction for the CMMC Program” provided new guidelines for CMMC 2.0. Our KTL webinar on Nov 10 helped to clarify these changes and answer questions for those in the DIB.

Read More »