SQL Server Wave

I have recently upgraded SQL servers from 2000 to 2008. SQL server doesn’t automatically upgrade the databases to the latest version ie 100.

So I had to change the version of each database manually using the following script:

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

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

To update all databases at once within the SQL instance, the following script might be useful.

select 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 100'
from sys.databases
where name not in ('master','tempdb','model','msdb')

That’s it.