SQL Server Performance

DTS Recovery

Discussion in 'SQL Server DTS-Related Questions' started by wolfenstein4u, Oct 30, 2007.

  1. wolfenstein4u New Member

    I have removed a SQL Server 2000 installation, as need to re-install the OS also backed up the whole "Microsoft SQL Server 2000" folder that contains all the MDF and LDF files of the DBs.
    Now i also had some DTS packages there but now when i have re-installed the whole setup; i could not get the DTS packages. Is there anyway to recover the DTS as i have the whole folder that contains the SQL Server information. DBs are attached again but only the DTS are left. There must be some place where SQL Server stores this information.
    Can anyone please help me out as its so urgent for my work.
    Mohsin Shahzad
  2. martins New Member

    All DTS package information sits within the msdb database, and the tables all start with sysdts...
    I hope you still have your previous msdb database files somewhere, otherwise you will not be able to get your DTS packages back. I would suggest that you restore/attach your previous version of msdb as something else and transfer the necessary data into the appropriate tables.
    There are also scripts available to copy DTS packages from one server to another. You might be able to modify it slightly to transfer it from your previous msdb database to the current one.
    Your other alternative would be to restore the previous version of msdb over your current msdb database. Read up on it first though, as there are certain steps to follow when doing this.
  3. wolfenstein4u New Member

    Thanks for the quick reply. I have the msdb backup file. Can you please tell me how to transfer it and whats the best way to deal with it.
    Also can i recover the jobs from this as well?
  4. martins New Member

    To restore your old msdb backup over your existing one, you should refer to MSDN. As I mentioned before, there are quite a few steps to follow and if you already created new DTS packages or Jobs you will lose them.
    Job info is contained within the tables (in msdb) that starts with sysjob...so yes, you can recover them either by restoring or transferring.
    I don't have any scripts available that will enable you to transfer DTS packages and jobs, but there are quite a few available on a number of sites. You should be able to modify these to transfer it all from the restored (and renamed) version of msdb to your current.
    Your other option would be to simply copy the data from all the mentioned tables to your current msdb tables. Be careful though, as you might encounter a few issues with database id's and owners. It is really up to you what you want to do. This last option might be worth a shot if you need a quick and dirty, but like I said you might pick up a few issues with that.
    The safest option would be to restore all your system databases, i.e msdb and master and there are different steps to follow for each (refer to MSDN).
  5. martins New Member

    Something to note before reinstalling SQL Server is to script all your jobs and save your DTS packages as structured storage files. This will enable you to easily get them back if you don't intend on restoring the system databases. [:D]
  6. wolfenstein4u New Member

    Thanks alot. Yes you are right that i must need to get the backup of these things in files; but i didn't know before that but now as i got stuck thats why will be careful as well. [:)]

Share This Page