• -- set compatibility level

    -- change dbowner to same as it was under SQL 2000, it is usually 'sa', but check

    -- update all statistics, needed for performance, SQL 2005 interprets these differently

    -- update usage stats (correct sp_spaceused values) can be incorrect after SQL 2005 upgrade

    dbcc updateusage(0)

    -- ensure torn page detection set.

    alter database dbname set page_verify checksum

    -- upgrade creates a schema for all users and sets this as default, use following to create SQL to

    -- modify users default schema to dbo and then run in a new window

    -- if SQL 2000 objects were not owned by dbo modify script accordingly

    -- SQL2000 always checks objects by username.object first, then checks dbo.object if not found,

    -- so this check can be avoided and improve performance by setting default_schema to dbo so it is checked first,

    -- however if all objects are owned by the user or all or some are owned by user and object not qualified with

    -- owner in SQL then default_schema should be left at user. Only testing may prove this.

    set nocount on

    select 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers

    where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0

    order by name

    -- now drop all the user schemas created

    select 'drop schema ['+ name+ '] ' from sys.sysusers

    where uid > 4 and issqlrole = 0 and isapprole = 0

    order by name

    -- if not done before backup used in migration, check database integrity, if comes up clean, data_purity will be enabled for all future checkdbs

    dbcc checkdb with data_purity

    -- if database reasonable size, take this opportunity to reindex it (run dbcc dbreindex maint plan job)

    -- if a large no of ad-hoc queries run on this database, consider forced parameterization option

    -- could reduce cpu usage significantly

    --alter database dbname set parameterization forced

    -- now back the database up

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