-- 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
---------------------------------------------------------------------