| Author |
Topic |
|
Marvin
Starting Member
2 Posts |
 Posted - 2003-05-07 : 03:49:52
|
| Hi,I've got a server with several DTS packages on. I'd like to know if there is a method that could allow be to backup all DTS packages associated to a server directly to a directory.The only way I know now (i'm a newbie in MS Sql Server) to save a DTS package is "open it - save it as".Is there a way to do that via a script or something like that ?Same questions for jobs.TIA |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-07 : 05:35:32
|
| Hi!You can:1) Backup the msdb database, which contains the packages on the server.2) Use the dts object library. The method DTS.package.SaveToStorageFile should help you do what you want. |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-05-07 : 05:54:33
|
| Use the DTS transfer wizard. Set msdb as the source and destination databases and "select * from sysdtspackages" on the source.HTHFranco |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-07 : 05:59:53
|
| The method Franco proposes does not work on Meta Data Services packages, just to clarify. |
|
|
|
ralphs
Starting Member
8 Posts |
Posted - 2003-05-07 : 06:01:17
|
| Hi,All DTS packages are saved in the msdb database. So if you backup msdb the dts packages will also be saved. But I you will also get everything else that is saved in msdb.I guess that you want to only save the dts package so you can restore them one at a time. there is VB prog. read more here: http://www.databasejournal.com/features/mssql/article.php/1462601If you like to do your own script, tip is to look at the systemtables in msdb and extract the information from the correct table/s. start by looking at msdb.dbo.sysdtspackages. Just in case test on a lab server :-) that you don't mind messing up ;-)Good Luck/Ralph |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-07 : 12:27:30
|
| All system databases should be part of a normal backup schedule. MSDB is a system database and contains DTS packages, jobs, and other stuff (see BOL for the info).Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-07 : 14:37:35
|
| Another option is to load the dts packages from files then you can just backup the directory - or use the sourcesafe to keep the backups.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2003-05-07 : 16:21:12
|
| You can also save the DTS Package directly on to the destination server by specifying the server name in the design mode |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-07 : 16:36:44
|
| Yes there are various ways of doing this, but I recommend backing up the MSDB database since you should already be doing it anyway to backup your jobs and other MSDB objects. To copy your DTS packages to another server, you should use the other methods mentioned.Tara |
|
|
|
Marvin
Starting Member
2 Posts |
Posted - 2003-05-09 : 02:45:57
|
| Hi,I think I'm gonna use the "SaveToStorageFile" method, because I d'ont only want to backup those DTS packages, but I also need to be able to transfer them to my ISP in a file format. |
|
|
|
|
DTS packages backup method