Introduction
I recently moved a database from SQL 2000 to SQL 2005. It was really just a matter of detaching the database, copying the files, attaching it on the new server, flip the compatibility mode, fix the user accounts, and run a few queries. Thats it? Could it have been so easy? It turns out the answer is no. There are some steps that need to be taken after moving a database to SQL 2005 or 2008 from SQL 2000.
The Script
This is an easy one so I am skipping straight to the meat. This is a simple script that runs DBCC UPDATEUSAGE, sp_updatestats, then runs a DBCC CHECKDB with DATA_PURITY.
Running update usage is something that everyone was supposed to do on a regular basis on SQL 2000. In SQL 2005 and up the usage is maintained using a different algorithm and *should* never need to be updated again. This BOL entry explains the functionality in detail.
There are a number of changes to the handling of statistics in SQL 2005 that are outlined here so the next step is to update statistics. Even if there were no changes in functionality I would probably update statistics here anyway. I cannot count the number of times updating statistics has yielded an unexpected boost in performance on high volume days. The new algorithm in 2005 and up first checks to see if statistics are out of date before updating them so it is also much less intrusive.
Finally, the last step is to run a DBCC CheckDB with the DATA_PURITY flag. I always like to make sure a database has not picked up any corruption on it’s journey between servers so I would typically run a CheckDB here anyway, but in this case it is more important. Data purity checks were introduced in SQL 2005 to make sure the data in columns conforms to the definition of the columns, like making sure a smalldatetime column did not contain a value like ‘0001-01-01 12:32:20.023’. This checking is not turned on by default when you move an older database to SQL 2005 / 2008, so to complete the migration process it is necessary to run a CheckDB with the DATA_PURITY flag turned on. Once the check is run and the database passes a flag will be set on the database and every future CheckDB will do the data purity checks. In my minde this is an essential step to fully completing the migration to SQL 2005 / 2008.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --Set Connection Settings to Avoid CheckDB Failures SET ARITHABORT ON SET QUOTED_IDENTIFIER ON SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF DECLARE @db_name sysname SELECT @db_name = DB_NAME() DBCC UPDATEUSAGE(@db_name) EXEC (@db_name +'..sp_updatestats ''RESAMPLE''') DBCC CHECKDB (@db_name) WITH ALL_ERRORMSGS, DATA_PURITY |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.