SQL Server Performance Forum – Threads Archive
sql 2000 to 2005 migration of logins & jobs i
i’m in the process of migrating my sqlbox from 2000 to 2005, to make it easier and down time less i’ve set up a new 2005 box. Can any one tell me / I need to know #I> what is the best way / script to transfer the logins and passwords fromsql 2000 (old server) to new server (2005). II> Script / best way to create the same jobs which are there in old box in the 2005 box. Summary : Is there any readymade script available which i can run in source (sql 2000box)which will give me the output which i can run in destination to get both done? also any link for this 2 above info /for best practice? Thanks
First question to ask, have you used UPGRADE ADVISOR?
Check the following links:
http://sqlserver-qa.net/blogs/tools…-upgrades-of-your-sql-server-environment.aspx
http://sqlserver-qa.net/blogs/perft…ow-after-upgrading-from-sql-2000-to-2005.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
hey satya thanks for the reply –
i did go through couple of articles wrt migration etc but they are quite lengthy… ie kind of not precise.. is there any way u can let me know or i can find out a way where i can get the reqd scripts.. / link where i can get such scripts….for this 2 purpose…
Basically you are risking it without any testing, you can take help of sp_help_rev_login in this case andhttp://vyaskn.tripod.com/moving_sql_server.htm fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
satya , thats good about login transfer part, what about script for jobs transfer ie 2nd part. Any link / script that will take care of generating a script by applying which in destination server ie 2005 it can create the same jobs and schedules as in the source server (sql2000). may be if possible getting the job history info as well. what is the best way to transfer the dts packages from sql 2000(old box) to sql2005 (new box) for upgradation.
You have to script them using Enterprise Manager in SQL 2000 and then recreate in SQL 2005. Right click on job to script, if there are many DTS packages and jobs then better to restore the MSDB databasehttp://msdn2.microsoft.com/en-us/library/ms143706.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
You can’t restore system databases including msdb…
So you have to migrate the dts packages where as jobs have to scripted and created on 2005. If you script the jobs in 2000 in EM OR using SQL DMO, script will work on 2005 to create the jobs including schedules…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>