Using Microsoft Sql 7.0 where are DTS jobs stored ?We
currently keep 80-100 dts jobs on
the server and I am planning my backup procedures. My SQL
person tell me that they are not stored any where in the
database ? Is this true? If my server failed and I
restored my databases Master, model pubs and
distribution from tape I wouldn't be able to reclaim
these jobs ?
..

There are four ways to save a DTS package: in SQL Server, in Meta Data
Services, in a Structured Storage File, and in a VB file. The default is to
save it in SQL Server, which means it gets stored in the MSDB database
(that's the one used for job scheduling and such).

Even if you're not saving the package in the server, you are still saving it
as a file of some sort, and it can (and should) be backed.

Per the BOL,
Saving a DTS Package to SQL Server
Save your Data Transformation Services (DTS) package to Microsoft® SQL
ServerT if you want to store packages on any instance of SQL Server on your
network, keep a convenient inventory of those packages, and add and delete
package versions during the package development process. This option saves a
DTS package in the sysdtspackages table in the SQL Server msdb database as
BLOB (binary large object) data.

You can save and delete versions of a SQL Server package. If a package has
multiple versions, you can display a version history in SQL Server
Enterprise Manager and open the version you want. Otherwise, the latest
package version is opened.

"Ed Redmond" <anonymous (AT) discussions (DOT) microsoft.com> wrote

In defense of your SQL person, technically they're right: the DTS job isn't
stored in a given "user" database. For example, if you have an Inventory
database in production use, and you write a DTS job to flow data in and/or
out, the DTS job isn't going to be stored in that Inventory database.

If you do take the default storage method in storing it on the server, then
it's to be found in a system database, MSDB, which also handles scheduling
for jobs. There are several tables that can be accessed, including one for
storing the DTS package as a BLOB, one for storing individual tasks in a
given package, and one for storing steps (tasks are what the package does,
and steps are the links from task to task to show when to do what task
next).

The BOL is a great resource. That is MS SQL Server's Books OnLine, and it's
a fine help-file. It's installed right with SQL Server.

Best regards.

"J O Holloway" <respond.to.group (AT) your (DOT) convenience> wrote

You would with a backup of MSDB though.

have a look at this also

DTSBackup 2000
(http://www.sqldts.com/default.aspx?242)

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ed Redmond" <anonymous (AT) discussions (DOT) microsoft.com> wrote