Check list for upgrading SQL Server

Below is my old post of upgrading SQL Server, there is a new check list published on codeplex.com SQL Check List which includes more information.

This is is a check list I used a while ago after upgrading SQL Server 2005 to 2008:

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 page_verify checksum with no_wait

4. Update usage
dbcc updateusage [database]

5. Update statistics
use [database]
go

sp_msforeachtable ‘update statistics on ? with fullscan;’
go

Note: Step 5 is recommended to done manually on large tables for accurate statistics periodically.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s