December 2, 2008 at 12:42 pm
Hi all,
One of our client is planning on migrating from 2000 to 2005 sql server. I'll have to advise on the steps they should follow for the migration. I always just take a backup of the database in 2000 server and restore the backup on the 2005 server using management studio and thats it. Will this be good for a migration process or is there anything else that needs to be done??
Thank you for your support.
December 2, 2008 at 12:44 pm
Also, though I know Master database is being used by all the other databases in the SQL Server 2000, our client use master database specific to each database. In this case, do they have to restore the master database as well in 2005 server??
Thank you.
December 2, 2008 at 12:52 pm
Here are some upgrade considerations.
First of all run the SQL 2005 Upgrade Advisor to make sure there are no compatability issues.
Restore the database to the new server.
Change the compatability level to 90.
For databases that have been upgraded from previous SQL Server versions, you must run DBCC CHECKDB with the DATA_PURITY option once, preferably immediately after the upgrade as follows:
DBCC CHECKDB ( ) WITH DATA_PURITY
If you upgrade a database from SQL Server 2000, the PAGE_VERIFY value will be NONE or TORN_PAGE_DETECTION. If it is TORN_PAGE_DETECTION, you should consider changing it to CHECKSUM.
Run DBCC UPDATEUSAGE.
Update Statistics.
Unless they are doing something very specific, you should not have to move anyting from the master database.
December 2, 2008 at 1:06 pm
Thanks a lot for the information.
December 2, 2008 at 1:34 pm
One minor detail in addition, run update statistics will full scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply