Yes, that will work. Another way is to build an instance of SQL2005 (on the same server or a different server), and then:
(a) "Generate Script" from SQL2000 for all User Accounts and Permissions
(b) "Generate Script" from SQL2000 for all Views, Stored Procedures and UDF's.
(c) Take the whole SQL server offline, ensuring no more transactions
(d) BACKUP all DB's from SQL2000
(e) RESTORE the SQL2000 datatabases into SQL2005
(f) Execute the Scripts from (a) and (b) above.
That does a good job for our medium-sized shop. For the DTS packages, you can simply install SQL2005 Service Pack 2, which includes something called the "Legacy Components". These will allow you to continue to use Legacy SQL2000 DTS packages on your SQL2005 server -- on the condition that you don't modify the DTS packages.
If you need to modify the DTS packages, that's the right time to convert them to SQL2005 SSIS.
Good luck with it.
"farrukh . pasha " <mssqldba-ezmlmshield-x20796712.[Email address protected] wrote:
Hey Qaryplum,
There are more then a few ways to do it. For moving Database you might
not face much of hassle. The way I started doing it is by:
1) detach / take one db offline
2) copy database files (mdf,ldf,ndf,etc)
3) attach to new sql2005 box.
4) after all db xfer, move users either with SSIS/Dts task (this moves
without user passwords) or by the famous Microsoft kb article
(http://support.microsoft.com/kb/246133).
After this comes the hard part the dts. For that you can:
1) Leave dts running on old sql2000 box while referencing the new db
2) Migrate the DTS to SSIS, (usually wizards does the simple dts
migration for you except few analysis service tasks and Activex tasks).
Hope that I helped.
Thanks.
FP
-----Original Message-----
From: garyplum
[mailto:mssqldba-ezmlmshield-x51971946.[Email address protected]
Sent: Friday, November 30, 2007 14:59 PM
To: LazyDBA Discussion
Subject: 2000 to 2005 upgrade
Hi
I've been asked to upgrade a SQL 2000 box of ours to SQL
2005 (most of our boxes run 2005); running Win2003 Enterprise Server.
Do I need to disable replication first & what are the major 'Gotha's' to
be ready for?
I intend leaving the 2000 DTS packages alone until I get my breath back
:-)
The upgrade advisor has flagged a few issues but nothing huge!!!
Best regards
Gary
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page