SQL Server Performance Forum – Threads Archive

Migrating the databases from SQL 2000 to SQL 2005

Hello All, Any body please help me what all the precautions we need to take when ever we are going to migrate the databases from SQL Server 2000 to SQL Server 2005. The scenario is :
Migrating all the databases from one server which is having 2000 Engine to other fresh server which has 2005 Engine. I am looking in to following:
1) What is the best way of transferring the user created SP#%92s, XP#%92s from master DB to new server master DB.
2) Best way of Login#%92s transfer
3) How to transfer all Jobs, DB maintenance plan, etcâ€¦ Please help me what all other things we need to take care while migrating the databases from 2000 to 2005. Thanks in Advance Mahi

Install SQL Server 2005 on a newly built server and migrate SQL Server 2000 master, msdb, model objects and recover databases on this newly built SQL Server 2005. For login transfer you can use SSIS or you can use sp_help_revlogin to get the logins/password from one to another.
I would suggest to script the jobs and recreate the maintenance plans in order to take more control. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Hi Satya Thanks for your reply, Can you please tell me the steps for migrating the objects of master, model and msdb databases to new built server. apart from the jobs, logins and maintanance plans any thing else we need to do for complete migration of server(all databases from 2000 to 2005)? Waiting for your reply,
Mahi Mahi

Hi Mahi, For migrating Master, msdb and model db you have to take back up from SQL Server 2000 and restore the same in SQL Server 2005.Before doing so you have first rebuild master db in SQL Server 2005 (Ref<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Server 2005 BOL for steps of rebuiding master db) and once you rebuild master db, you can simply restore other db. Note: Please try steps in test environment before going to production. Thanks and Regards Ravi K.

]]>