DB upgrade to 2008R2 checklist

Before upgrade:

- Backup database

- Run Upgrade Advisor

Run upgrade advisor for all databases (including system databases) and fix any issue/blocker for upgrade.

http://msdn.microsoft.com/en-us/library/ms144256.aspx

http://geekswithblogs.net/akshaylamba/archive/2010/12/22/overview-of-microsoft-sql-server-2008-upgrade-advisor.aspx

Error fix: SQL Server 2008 Upgrade Advisor fails to connect to an instance:

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/cfb5003a-a039-49b5-a94a-2699c03cc33a

I Skipped this error by enter "default" to the named instance.

After upgrade:

- Update statistics (to ensure performance)

sp_updatestats

- Check Collation

- Check compatibility level

Set compatibility level to 100, by the DB Properties window or by command.

EXEC sp_dbcmptlevel DBNAME, 100

- Updating page or row counts or both for all objects in the current database

DBCC updateusage 0 -- 0 = current DB

http://technet.microsoft.com/en-us/library/ms188414.aspx

- Recreate full text indexes removed from the databases.

- Re-build Full-Text catalogs

ALTER FULLTEXT CATALOG [CATALOGNAME] REBUILD

- Test the application with the new database server 2008.

- It's recommended to check definitions of Users, Schemas, Rules in the new server.

More links:

http://msdn.microsoft.com/en-us/library/bb677622.aspx

http://technet.microsoft.com/en-us/library/ms144267.aspx

http://www.sqlservercentral.com/articles/Upgrade/65872/