LearnItFirst User Forum

USE master
GO
CREATE PROC dbo.DBA_UpgradeSql2000Database (@DbName SYSNAME)
AS
/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This proc updates the database settings for any database upgraded from SQL Server 2000
        to be the recommended/generally-used settings in SQL Server 2005.  When you restore a database
        from SQL Server 2000 onto SQL Server 2005, the database settings may not be ideal. This proc ensures
        consistency among databases.

    Misc Notes: One major thing you need to do is to change the compatibility mode/level of the "new" database.
        This proc cannot do that because you are not allowed to execute sp_dbcmptlevel outside of any other
        context; i.e. you can't execute sp_dbcmptlevel from inside a stored procedure for example.
   
    Versions: SQL Server 2005
   
    Creation Date: August 31, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx

    To make it easier to call this proc, this allows 'Northwind', "Northwind", [Northwind] or Northwind;
    they all get treated the same.

    @DbName_Formatted = [Northwind] for the ALTER DATABASE statement
    @DbName is left the same because, for whatever reason, sp_helpdb cannot use @DbName_Formatted
            although you can pass in EXEC sp_helpdb [Northwind] and it works fine
*/
DECLARE @DbName_Formatted SYSNAME
SET @DbName_Formatted = REPLACE(@DbName, '''', '') -- replace single quotes
SET @DbName_Formatted = REPLACE(@DbName_Formatted, '"', '') -- replace double quotes
SET @DbName_Formatted = REPLACE(@DbName_Formatted, '[', '') -- replace [
SET @DbName_Formatted = REPLACE(@DbName_Formatted, ']', '') -- replace ]
SET @DbName_Formatted = '[' + @DbName_Formatted + ']'

-- Can't call sp_dbcmptlevel from inside another proc; must do this separately
-- EXEC sys.sp_dbcmptlevel @dbname=@DbName, @new_cmptlevel=90

DECLARE @DynamicSql NVARCHAR(1024)
SET @DynamicSql = '
ALTER DATABASE ' + @DbName_Formatted + '
    SET ANSI_NULL_DEFAULT ON
    , ANSI_NULLS ON
    , ANSI_PADDING ON
    , ANSI_WARNINGS ON
    , ARITHABORT ON
    , AUTO_CREATE_STATISTICS ON
    , AUTO_UPDATE_STATISTICS ON
    , NUMERIC_ROUNDABORT ON
    , PAGE_VERIFY CHECKSUM
    WITH NO_WAIT
'
EXEC sp_executesql @DynamicSql
PRINT @DynamicSql

EXEC sp_helpdb @DbName
GO
EXEC DBA_UpgradeSql2000Database joplin
EXEC sys.sp_dbcmptlevel joplin, 90