Saturday, March 07, 2009

SQL Server 2000 post upgrade steps

Small post on additional steps to perform when upgrading from an existing SQL Server 2000 database to SQL Server 2005 / 2008 (after you backup’ed, run upgrade advisor and tested everything twice right!)

After a successful restore or attach database operation, execute the following commands in the new environment:

USE your_db_name
DBCC UPDATEUSAGE ('your_db_name')
EXEC sp_updatestats

- Set database compatibility level to ‘SQL Server 2005’ or ‘SQL Server 2008’
- Set Page verify option to Checksum
- Set 'Auto Create' and 'Auto Update' statistics to TRUE
- Reorganize all indexes

Running ‘DBCC UPDATEUSAGE’ and ‘DBCC WITH DATA_PURITY’ once is enough.
The DBCC CHECKDB of the newer (2005/2008) SQL Server versions has it all combined in one statement.

Upgrade to the the newer varchar(max), nvarchar(max), and varbinary(max) data types instead of text, ntext, and image data types.
You can check by executing this T-SQL on your upgraded database on SQL Server 2005 / 2008, to see which tables need some work:

USE = 'your_db-name_here'

SELECT tablename, columnname, columntype FROM sys.all_columns columns INNER JOIN sys.all_objects userobjects ON columns.object_id = userobjects.object_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id
WHERE userobjects.[type] = 'U'
and IN ('image','text','ntext')

Bookmark and Share