June 3, 2004 at 8:41 pm
Hello All,
I'm in the process of building a new physical SQL 2000 server. Once the server is finished and SQL 2k has been installed, I want to move the databases and all associated objects (local DTS packages, jobs, logins, backup devices) from the old server to the new. I plan to reuse the name of the old server for the sake of the user and developers. Moving the user databases is not a problem (detach and attach). Moving the system databases is a bit challenging but I have a MS article which describes how (Q224071). Moving the remaining objects is where I need help.
How do I move Local DTS packages, jobs, and backup devices from the old server to the new? Is there a way that I can package them up?
Thanks in advance. Any help is greatly appreciated.
Ronnie
June 3, 2004 at 10:27 pm
DTS has a transfer jobs task.
And for the DTS packages, I usually backup msdb from the "old" server and then restore it with a different name to the "new" server. Then it is simply a matter of running the following insert :
Insert Into msdb.dbo.sysdtspackages Select * from oldmsdb.dbo.sysdtspackages.
Select * from master.dbo.sysdevices where status = 16
will show you all of your backup devices. You should be able to generate a script that uses sp_addumpdevice so that you can create them on the new server.
Maybe something like
Select 'exec master.dbo.sp_addumpdevice ''disk'', ' + Name + ', ' + etc
From master..sysdevices
Have a look at sysdevices in BOL. It tells you how to decode the data in sysdevices.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply