moving databases ,jobs and dts pckgs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

moving databases ,jobs and dts pckgs

i have installed new sql 2005 enterprise edition. now i have to move four-five very large databases of nearly 400 gb each from sql 2000 to sql 2005. -i have to move these databases along with their respective jobs, and the most important dts packages. – i read an article to detatah and re atach the database. but please guide me as per your experience what is the most reliable way to
move databases from sql 2000 to sql 2005 along with jobs and dts packages. i am using sql 2005 for the first time so please try to give idea in little detail please.
i highly appreciate for your patience and time.
You can do the backup and restore to copy the databases or you can use attach/detach option also…
But detach/attach is offline operation where backup/restore is online operation means you can move dbs with less downtime… — You can’t restore system databases so script all the jobs and run the script on 2005 to create the jobs. — You can migration DTS packages but if the dts packages have any activex script or special transformations…they will migrate properly… so it is advisable to create SSIS packages… — If you want to run the DTS packages without migrating them to SSIS then you have install DTS add-ins (Microsoft SQL Server 2000 DTS Designer Components ) from the following link on sql server 2005 server. http://www.microsoft.com/downloads/…94-8453-4998-8226-fa42ec403d17&displaylang=en SQL Server 2005 Upgrade Technical Reference Guide
http://www.microsoft.com/downloads/…d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
detachattack method will only database scoped objects. Jobs and DTS are to be explicitly transferred. the best way to move JOBs is to script the job and run the script in the destination server. As far as DTS is concerned… though SQL Server 2005 support DTS for backward compatibility and there are ways to run DTS in 2005, in the longrun you may need to recreate them in SSIS. Refer these links : http://msdn2.microsoft.com/en-us/library/ms143706.aspx
FAQ :http://msdn2.microsoft.com/en-us/library/ms345120.aspx http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1216481,00.html
Madhu
First question is have you tested the Upgrade from 2000 to 2005 on your test platform?
If not you are risking a lot without knowing the complications, until unless you have tested the approach it is nothing but black-hole on the issues. Also test the compatibility of the applicaton in this case. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
thanks a lot friends actually i am using this server to generate reports.
so how i can test can you please give an idea and
will i face problme in restoring the sql 2000 user database on sql 2005 i got your point regarding the databases and jobs. can you please suggest me , actually at present
i am running backup with stored procedure and creating job. so does backup method is different in sql 2005 or if i create a sp for full backup and trans backup . then if i create jobs for that will my jobs for the full backup and
trans backup will be successful. i will restore the user databases as you suggested and will run the script of the jobs as
also suggested. but regarding dts, i have not got it. how i can create the same dts packages in sql 2005 ssis
http://www.sqlservercentral.com/columnists/bknight/upgradingsqlserver2000dtspackagestossis.asp
http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/ http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx- upgrade hand book 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
There is no difference in backup restore methods it is the same as in sql 2000 …
Your procedure should work without any issues… For dts packages for the links provided in the previous posts…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Manu
I believe you need a total refresh or more insight on SQL 2005 features, take help of SQL 2005 in this case about deprecated, backward compatibility and upgrade information that will clear lot of your doubts. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I will read these article and do good research as you all suggested before working on sql 2005 ssis. please let me know if you know about any other weel explained articles
on sql 2005 regarding moving everything from sql 2000 to sql 2005. Thanks once again to everyone.
Search on the forums for relevant upgrade and starter threads that will give much idea on how to resolve the problem, it will be easy to see when you get similar ones. Also that saves your time in posting repeated questions here, if the problem really typically and not solved here before then you are most welcome. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Read the upgrade article mentioned above…
and if you want to hands on try the following… TechNet Virtual Labs: SQL Server 2005 Upgrade
https://ms.helifan.net/technet/traincert/virtuallab/sqlupgrade.mspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
http://sqlserver-qa.net/blogs/tools/archive/2007/04/12/set-a-stage-for-smooth-upgrades-of-your-sql-server-environment.aspx for the sake of smooth upgrade from 2000 to 2005. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>