SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio

I have received quite a few request about post I have two days ago SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility, if this can be done using SQL Server Management Studio. It is very simple to do this using Management Studio as well but I still prefer T-SQL way.

Following steps will display the method to change the compatible levels.

Write click on database.
Click on Properties.
Click on Options.
Change the Compatibility level to desired compatibility. (See Attached image below)
Click OK.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio

  1. I tried changing it like that, but the SQL Server 2005(90) option is not available. Any Ideas????

    Plz help i’m struggling.

    regards

  2. Arthur,

    Please provide more information. Which version you are working with, which database. Is it read only? Is it system Database? Screenshot of the your case will also help.

    Regards,
    Pinal Dave (SQLAuthority.com)

  3. Hi

    I have SQL 2005 server with some 80 or higher compatibility databases and some ae of 70 and lower. When i try to create backup job using database maintenance plan, it takes only 80 and higher compatibility databases and skips all lower compatibility databases.

    Can some help me ?

  4. Hi

    I have the same problem. I am being forced by the application provider to set the compatibility level to 7.0 for the database so how do I setup a maintenance plan?

    Thanks

  5. Hi,

    I am using SQLServer Management Studio 2005. I have a database located in remote machine, which is SQLServer 2000. When i am trying to connect to that server, it is saying that, SQLServer doesnt exist or server doesnt exist. If i try the same way from some other machine with the same SQLServer 2005 installation, it is working fine. Does anyone have any idea on the compatibility issue?

    Thanks,
    Suren

  6. Hello Sir,

    Is there any implication or effect if I change the compatibility of the database if it used to be in 70 level and i will change it to 90 level.

    Also, will there be an effect if I change the Recovery Model from Simple to Full?

    Thanks

    • Hi,

      Changing the compatibility level changes the syntax validation, and execution bahaviour of a database. So test the implications on a testing database first and if all connecting applications work fine then chnage to production database.
      Changing the Recovery model to FULL does not affect the functionality and execution of database but without any future plan it can cause the large log file size and disk full issue.

      Regards,
      Pinal Dave

  7. Hi Sir,

    BTW, thanks for all your blogs! since i am a newbie DBA, reading you blogs helps me a log in my work.

    I learned a lot!

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s