Search code examples
sql-serversql-server-2008sql-server-2005compatibility

Compatibility level for SQL Server database

My database hoster allows to restore SQL Server databases with a compatibility level of 90 (SQL Server 2005). My database is created locally with a compatibility level of 100 (SQL Server 2008).

So, I generated script of my database (version 2008) and run in SQL Server 2005, backup and restore to my database hoster, it works. Currently I do likes it.

And then I found ALTER DATABASE that can change compatibility level of database likes

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }

 90 = SQL Server 2005
 100 = SQL Server 2008 and SQL Server 2008 R2
 110 = SQL Server 2012

I turn my database compatibility level using this script in SQL Server 2008, backup my database and restore to my database hoster. But it doesn't work. I want to know why? Is there a better way to fix it ?

Solution

  • You can't go back to version compatibility of sql server better to go through scripts.