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 (https://blog.sqlauthority.com)
9 Comments. Leave new
I tried changing it like that, but the SQL Server 2005(90) option is not available. Any Ideas????
Plz help i’m struggling.
regards
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 ?
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
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
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
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!
Are the databases backward compatible