Advertisement

Checking Compatibility Level Of All Databases In SQL Server

Share the blog:

Checking Compatibility Level Of All Databases In SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a server-based system that can be accessed by multiple users simultaneously. It offers many features, including data security, scalability, and high performance.

One important feature of SQL Server is its compatibility level. This refers to the ability of the database to run different versions of SQL Server. For example, if a database has a compatibility level of SQL Server 2012 (110), it can run on any version of SQL Server from 2012 onwards. However, it cannot run on an earlier version of SQL Server.

The compatibility level can be changed by using the ALTER DATABASE command. This can be useful if you want to upgrade to a new version of SQL Server but you’re not ready to make all your databases compatible with the new version. You can change the compatibility level back to the previous version at any time.

It’s important to check the compatibility level of all databases in SQL Server because it can impact performance and stability. Incompatible databases can cause errors and crashes. Therefore, it’s best to ensure that all databases are compatible with the current version of SQL Server before upgrading.

Checking the compatibility level of all databases in SQL Server

As organizations move to newer versions of SQL Server, they should check the compatibility level of all their databases. The compatibility level determines which version of SQL Server a database can be used with. To check the compatibility level of all databases on a server, run the following query:

Solution

Here is a simple TSQL to simplify the work:

Query

Steps

To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

Result

Scenario

To alter the compatibility level of a database

Solution

Here is a simple TSQL to simplify the work:

Query

Facebook Comments Box
Share the blog:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Advertisement