July 3, 2009 at 8:45 am
i'm currently doing a practice run in upgrading a 2000 database (32 bit) to sql server 2005 (64 bit). The installation will be side by side so i don't overwrite my old database.
My procedure is outlined below and starts by taking a backup and restoring the databases onto a sql server 2005 database (note i have already ran upgrade advisor and fixed the T-SQL)
0. Once database is available change compatibility if database to 90.
1. Alter the database to set Page_Verify to checksum.
2. Create temp clustered indexes on all tables which don't have any to ensure checksum is added to underlying pages.
3. Rebuild all the indexes to ensure all pages have checksum (via DBCC DBREINDEX or Alter Index) - this will also update all the statistics so no need to do update statistics WITH FULL SCAN etc on tables
4. Drop the temp indexes
5. Run CheckDB with the data_purity option
6. Run DBCC updateusage - To update stats in the catalog view.
For those who have done this before , am i missing any steps or think i'm going about this wrong or a bit "overboard"
July 3, 2009 at 9:02 am
You still need to do an update stats. Rebuilding the indexes will update stats on those indexes. It won't update column statistics that aren't associated with an index though. Like the stats that SQL automatically creates
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 9:18 am
thanks gail , that makes sense , i assume the bit about forcing the checksum on all the pages by creating the clustered index where not present and re-
building theses indexes is fine?
July 3, 2009 at 9:19 am
I think steps 1-4 are a bit paranoid.. And this coming from a paranoid techie... The database will be a mirror of the 2000 with some underlying updated structures. If you had a problem in the 2000 db structure it will still be there OR the DB will not restore..
CEWII
July 3, 2009 at 11:59 am
Elliot , its not a question of being paranoid or that the structure of the restore may be corrupt. Its more about complying with the sql server checksum page verification model and upgrading a database as clean as i can. i would rather have all the pages associated with the upgrade database with the checksum added rather than when or "if" a page is picked up by sql server.
July 4, 2009 at 11:48 am
Well then I'm guessing that it is alright then..
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply