Upgrade your databases to SQL 2008 R2 (script)

I think I have upgraded SQL servers and databases more often than I have eaten hot dinners.

So I have created a SQL script that will do the following:

1.       Set Auto_close to off (makes your DBs faster)

2.       Set Auto_shrink to off (the Auto_shrink feature gives your SQL server a performance overhead, and the DBs will grow to the same size anyhow afterwards. Use db_shrink for shrinking your DBs)

3.       Set Parameterization to forced (optimizing queries)

4.       Set Auto_create_statistics to on (helps your SQL server to query more intelligent)

5.       Set Page_verify to checksum (gives you the best security for dirty pages and corruption)

6.       Set Compatibility_level to 100 (this is the compatibility level for SQL 2008 (80 is MSDE, 90 is SQL 2005, so when you upgrade to SQL 2008/R2, also upgrade the compatibility level))

In general, you can use this stored procedure for setting dboptions on your databases:

----------------------

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'') '

---------------------

In my script, I will not touch the recovery model for the databases, since this is not me to decide, but the the organizations backup policy.

I also have to mention that you should set the options properly on the ‘Model’ database as well, since the databases you create will inherit the settings from here.

The script will run in SSMS 2008 (just copy the content to notepad, and save the as sqlscript.sql

--------------------------------------------------------------------------------------------------------------------------------------

--SQL script from Virtualization and some coffee

--Auto_close, auto_shrink = off, Parameterization = forced, Auto_create_statistics = on, Page_verify = Checksum, Auto_update_statistics = on

--Sets compability level to 100 - SQL 2008

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

  

EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_SHRINK OFF WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET PARAMETERIZATION FORCED WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT'')

'

DECLARE GET_DATABASES CURSOR 

READ_ONLY  

FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '100' 

 

DECLARE @DATABASENAME NVARCHAR(255)  

DECLARE @COUNTER INT 

 

SET @COUNTER = 1  

 

OPEN GET_DATABASES  

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  

WHILE (@@fetch_status <> -1)  

BEGIN 

IF (@@fetch_status <> -2)  

BEGIN 

-- Endrer db-compatibility  

EXECUTE sp_dbcmptlevel @DATABASENAME , '100' 

 

PRINT  @DATABASENAME + ' changed' 

 

SET @COUNTER = @COUNTER + 1  

END 

 

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  

END 

 

CLOSE GET_DATABASES  

DEALLOCATE GET_DATABASES  

 

GO