KTL SOLUTIONS

Setting SQL Server Database Compatibility level for all databases

Share this post

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.

Related Posts

Checking Your CMMC Progress

Written by Alec Toloczko With Cybersecurity Maturity Model Certification (CMMC) requirements on the horizon, it’s crucial for organizations handling Controlled Unclassified Information (CUI) to adhere

Read More »