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.

Command Line Usage

The individual command line switches are detailed below -

/B (Backup) : This starts a Backup operation (This switch has no parameters).

/R (Restore) : This starts a Restore operation (This switch has no parameters).

/S server_name : The name of your SQL Server.

/p path_directory_name : The path to place the COM files or restore from.

/s Storage_Type : S = Server, R = Repository. Leave switch out to backup both. Required for restore.

/U Username : Leave switch out to use NT Integrated security.

/P Password : As above.

/C (Command Line) : Activates Command Line mode (This switch has no parameters).

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.

Notes:

Switches are case sensitive
If you backup a server and repository package of the same name, they will overwrite each other. To overcome this, run it separately for each, specifying a different path.
If you restore two packages of the same Package Name, they will overwrite each other. Similarly a restore will overwrite an existing package of the same name and ID.
Please ensure the account you run this from has correct file permissions to the path location. This is especially important depending on the service account used for SQL Server Agent.
Package names that have invalid characters e.g. \ / : etc will have these replaced with a # symbol. This does not alter the Package Name, just the file name.

GUI Usage

Once connected you will presented with list of DTS Packages on the server.

To Backup -

Select packages to backup
Set backup location directory
Click Backup

To Restore -

Check File Packages
Set backup location directory
Select Server or Repository for restore location
Select files to restore
Click Restore

Download

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.

Any comments would be greatly appreciated.
Download DTSBackup (~25K).

Version History

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

DTSBackup Utility (VB)

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos