DTSBackup Utility (VB)
December 28, 1999
This small application allows you backup DTS Packages by saving them to COM-structured storage files. This can be done for both server based packages and those stored in the repository.
COM-structured storage files can also be imported or restored, back into the server or repository.
Backing up the msdb DB will backup your DTS Packages, but this makes it very difficult to restore just a single package. The msdb also controls the SQL Server Agent so restoring the whole of msdb from two weeks ago could be very messy and quite possibly dangerous. If you schedule this regularly, it will save all packages to COM-structured storage files which are easily retained as part of a normal file backup to tape routine. No mess, no fuss. This is also an easy way of transferring packages between servers, and probably the only way if the servers are not on the same network.
There is a command line interface which can be used to automate backups and restores, e.g. Scheduling the processes via SQL Server agent. There is also a GUI front end for when you want to perform an ad-hoc backup/restore.
To run this you will require Microsoft Visual Basic 5 Runtimes, Microsoft ActiveX Data Objects 2.1 Library and the Microsoft DTS Package Object Library installed on the local machine. The latter two come with SQL Server and the VB 5 Runtimes can be downloaded from the Microsoft site.
Please note that DTSBackup does not support owner or operator passwords in DTS packages. The main reason for this is that when backup or restoring a DTS Package, I load the package. To do this requires a password for encrypted packages. This is fine for GUI but how can I accomplish this via the command line? All suggestions welcome.
COM-structured storage files can hold multiple versions of a package. When performing a backup, if the file already exists, the current backup will be appended to that file. When restoring, the most recent version will be restored. To access previous versions, use Enterprise Manager to open the file. Multiple packages can be saved to the same COM file, however this is not recommended with DTSBackup.
The individual command line switches are detailed below -
Backup Example :
DTSBackup.exe /B /S MY_Server /p C:\DTSBackup\Files\ /C
This will backup both server and repository packages to the location specified, using NT Integrated security.
Restore Example :
DTSBackup.exe /R /S MY_Server /p C:\DTSBackup\Files\ /U sa /P Abc123 /C
This will restore all file packages from the location specified, to the server as (server packages), using SQL Server Security.
Once connected you will presented with list of DTS Packages on the server.
To Backup -
To Restore -
Please note that this program is provided without any warranty or assurances of any kind.
Use it at your own risk. Commercial distribution or distribution for profit in any form is strictly forbidden without prior permission from the author.
Version 1.2.0 (28/12/1999) - Fixed bug with Storage Type command line switch (Repository option). Updated connection code to use explicit Trusted_Connection option as appropriate.
Version 1.1.3 (18/09/1999) - Fixed bug that caused DTSBrowse to crash when restoring packages to empty server/repository.
Version 1.1.0 (05/09/1999) - Fixed bug in backup of repository packages. Fixed hyperlink on about screen.
Version 1.0.4 (28/08/1999) - Restore from file package functionality added. New [/B] or [/R] switch required to support restore.
Version 0.6.3 (26/08/1999) - Fixed SQL Server security logon error. Added function to replace all invalid file name characters with # symbol. (Error - "Run-time error '-2147287037 (80030003)': The system cannot find the path specified")
Version 0.5.5 (22/08/1999) - First Public Release Version