Post upgrade steps for SQL Server 2008

Question

Post upgrade steps for SQL Server 2008

klnsuddu

SSCertifiable

Points: 5836
More actions
June 17, 2010 at 12:37 pm

#222879

Hi,
I have Upgraded the SQL Server 2005 instance to SQL Server 2008 SP1 using side-by-side upgrade method and performed the below Post upgrade steps and want to make sure the order of the steps are correct or not? and do I need to perform any other Post upgrade steps? please advice me
1. Change the Compatibility mode to 100
2. Run DBCC UPDATEUSAGE
3. Run DBCC CHECKDB(DBNAME) WITH DATA_PURITY
4. Run DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS
5. Run SP_UPDATESTATS for all database
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

gary1 SSC-Insane Points: 21152 More actions · Answer 1

1. Change the Compatibility mode to 100
2. Run DBCC UPDATEUSAGE
3. Run DBCC CHECKDB(DBNAME) WITH DATA_PURITY
4. Run DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS
5. Run SP_UPDATESTATS for all database

You can also include rebuild indexes as a post upgrade step after Step4.

David Sorauer SSCrazy Points: 2874 More actions · Answer 2

That looks pretty good to me.

I like to also compare row counts before and after to prove no records were lost as part of the upgrade process.

I agree with gmamata7, I would rebuild indexes if time permits.

You may wish to implement some of the new SQL 2008 features (especially compression) if you have performed analysis and testing on these features previously.

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 3

@klnsuddu ,

you can compare the database login between SQL 2005 and SQL 2008.

Also, as a end step, you should take a backup of Database in sql 2008.

nogoodboyo SSC-Addicted Points: 408 More actions · Answer 4

If you rebuild the indexes, you can skip the stats update.

From BOL:

Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.

Mani-584606 SSChampion Points: 11788 More actions · Answer 5

2. Run DBCC UPDATEUSAGE
3. Run DBCC CHECKDB(DBNAME) WITH DATA_PURITY

when you upgraded from SQL Server 2005 to 2008, are the above two steps required? if Yes then why we need to do that?

please advice