a scheduled job that creates backup of DTS jobs

Question

a scheduled job that creates backup of DTS jobs

ann ramos

Right there with Babe

Points: 791
More actions
June 21, 2005 at 7:41 pm

#102603

hi everyone,
can someone point me in the right direction on how to go about creating a scheduled jon that creates a backup for all the DTS that I created?

thanks and regards.

ann

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

DTS packages reside in the msdb database. So they're backed up when you backup the msdb database (you do back it up regularly don't you ?? )

If you want to backup the packages seperate from the database, then I'd suggest grabbing hold of DTSBACKUP2000 from http://www.sqldev.net. This handy tool has a gui and command-line interface and can transfer packages from file to server, server to server, etc...

For your scheduled backup your would just set it up in a scheduled job with the appropriate command-line parameters.

--------------------
Colt 45 - the original point and click interface

ann ramos Right there with Babe Points: 791 More actions · Answer 2

got it.

another question though. what's the best way for me to backup my dts. should i include the msdb in our regular backup? coz, the way we're doing it at the moment is backing up the database that contains all our transactions. when something happens to the server i can just install a new sql into a new server and restore the last backup that i got.

i understand that if i backup the msdb i need to do a series of other stuff before i can restore it to my server.

is it also important to backup the master database?

i'm fairly new into this and would appreciate inputs from experts =)

ann

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Ann,

Yes, you have to backup Master. It contains all information about other databases and most important, logins and passwords.

You absolutely have to backup MSDB if you have jobs and DTS packages.

Yelena

Regards,Yelena Varsha

philcart SSC-Forever Points: 47794 More actions · Answer 4

Ann

The time, effort and resources you expend backing up, and possibly restoring, the system databases is vastly outweighed by the consequences of not doing it. I'm of the opinion that all system databases should be backed up on a daily basis as a minimum. If you have a lot of DTS packages and scheduled jobs, you could even backup msdb more often.

--------------------
Colt 45 - the original point and click interface

abeljda Say Hey Kid Points: 675 More actions · Answer 5

Hello, here we also use SourceSafe to manage our DTS packages saving them as a sturctured storage file. That way we have additional version control along with knowing they are stored on the network and backed up, granted, sourcesafe has issues of its own...but it has come in handy having it in a couple of places.