• All steps apart from 4 are now in Brian's list, Louis if you disagree please start a discussion thread (private to the project).

Just a starter: this is something I used from long time ago after upgrading SQL Server 2005 to SQL Server 2008. I think first 3 items are longer needed but last 2 still stand.

1. Update compability level:
alter database database set compatibility_level = 100

2. check data purity
dbcc checkdb database with data_purity

3. Update page verification
alter database database set pageverify checksum with nowait

4. Update usage
dbcc updateusage database

5. Update statistics
use database
sp_msforeachtable 'update statistics on ? with fullscan;'

Also, it would also useful to rebuild indexes

I think:-
1. Changing the compatibility level is needed, not automatically updated.
Louis -- Agreed.

2. A SQL 2005 database could have purity errors (I have seen them), similarly a database upgraded to SQL 2008 could still have the SQL 2005 purity errors so should run this.
Louis -- Agreed.

3. Again I would do 3.
Louis -- Agreed again.

4. I do not think dbcc updateusage is required this was for pre 2005 and early service pack upgrades for SQL 2005.
Louis -- I understand you came from and I still think it could be a optional step to run it once before promote to production. Open to suggestions.

Last edited Jan 21, 2014 at 1:54 AM by LouisLi, version 3