Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify Databases Not in SQL Server 2008 Compatibility Mode


By:   |   Last Updated: 2010-08-06   |   Comments (1)   |   Related Tips: More > Policy Based Management

Problem

You need to periodically ascertain if there are any databases in your production environment which are not in SQL Server 2008 Compatibility Level. In this tip we cover the step by step process for setting up a policy to identify databases that are not in SQL Server 2008 Compatibility Level.

Solution

Database Administrator can use the Policy Based Management (PBM) to identify databases which are not in SQL Server 2008 Compatibility Level. You can follow the below mentioned steps to create and evaluate the policy which can be used to identify all databases which are not in SQL Server 2008 Compatibility Level.

If you are not familiar with Policy Based Management (PBM) then you can start with the following tip: Using Policy-Based Management in SQL Server 2008.


1. Connect to a SQL Server 2008 Instance using SQL Server 2008 Management Studio and expand Management -> Policy Management and then right click Policies and select New Policy... as shown in the snippet below.

Connect to a SQL Server 2008 Instance using SQL Server 2008 Management Studio

2. In Create New Policy window provide the name of the policy as "Identify Databases Not in SQL Server 2008 Compatibility Level". In Check condition you need to click New condition.... to open up Create New Condition window.

 "Identify Databases Not in SQL Server 2008 Compatibility Level"

3. In Create New Condition window you need to provide the name of the condition as "Databases Not in SQL Server 2008 Compatibility Level" and then select the Facet "Database". Under Expression select Field value as @CompatibilityLevel and choose operator value as ' = ' and then value as Version100 as shown in the below snippet. Click OK to save the condition and to return to the parent Create New Policy window.

Under Expression select Field value as @CompatibilityLevel and choose operator value as ' = ' and then value as Version100

4. In Create New Policy, by default Every Database option under "Against targets" will be checked as shown in the below snippet. Click OK to create the policy.

 by default Every Database option under

5. Once your policy is created, the next step will be to evaluate the policy. Right click the policy and select the Evaluate option from the drop down list as shown in the below snippet.

select the Evaluate option

6. The below snippet shows the result set returned by the policy. We can see that for one of the database AdventureWorks the policy has failed. The reason for the failure is the AdventureWorks database was restored on SQL Server 2008 environment from the backup which was taken from a SQL Server 2005 environment.

The below snippet shows the result set returned by the policy

7. When you click a "View..." link under Details, you will be able to see the detailed result for a particular database. For this database the expected value was Version100, however the current compatibility level for the database was Version90 which is that of SQL Server 2005.

the current compatibility level for the database was Version90 which is that of SQL Server 2005

8. If you want to change the compatibility level for the database that is not set to Version100, check the target database and click the Apply button. This action will pop up a Policy Evaluation Warning as shown in the below snippet. Click Yes to change the compatibility level of the database to Version100.

check the target database and click the Apply button

9. Once the policy suggestion is applied you will be able to see the below screen that shows you are in compliance. Click Close to exit.

see the below screen that shows you are in compliance

Next Steps


Last Updated: 2010-08-06


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 16, 2010 - 5:03:34 AM - Mick Back To Top
Nice article Ashish,

 

but just a quick one on a simialar note. I,m using Visual Studio 2008 to build my MS SQL databases as I,m a developer. We use two types of MS Server, 2008 Express and 2008 Standard. I have some scripts that will only run on the Standard version like scheduling jobs but want to check in my script the version or type of the 2008 that I have installed. How can I do this.

 

Thanks Mick


Learn more about SQL Server tools