When a database consultant finds a tool that's useful in a variety of situations, that tool can be invaluable. SQL Server consultant and contractor Adam Thurgar has found a winning DTS tool that has a particularly attractive feature: it's free. DTSBackup 2000, a downloadable tool, was the key to solving a major package-moving problem that Adam faced in his current job.
In his contracting work with a large financial institution, Adam found himself working in an environment that included more than 350 databases on approximately 60 SQL Servers. The servers were running a mixture of SQL Server versions, including SQL Server 2000, 7.0, and 6.5. The shop even included a dedicated test server running SQL Server 2005.
Adam's challenge in this environment was to move 300 databases to a new server; some of these databases were transactionally replicated and some were log shipped. This task, a giant under the best of circumstances, was complicated by the fact that many of the databases were linked to realtime systems that used realtime data feeds and had to be highly available during trading hours. In addition, the packages that Adam was to move were involved in creating cubes and dimensions for financial analysis and month-end reporting, so Adam had a month-end deadline for moving the packages to the new server.
According to Adam, the task "looked to be a time-consuming, manual process." He explains that he initially considered two possible solutions: "We could either open each package and do a SAVE AS to the new server, or we could try to do an INSERT SELECT from the relevant table in msdb. Neither solution was elegant or foolproof."
Adam ended up looking for another answer online. He searched the Internet using the keywords "DTS save" and "DTS copy" and repeatedly got links to DTSBackup 2000, a small, popular download at http:// www.sqldts.com. According to site author Darren Green, the tool is designed "to help with both backup and transfer of DTS packages." Although the site doesn't recommend that you use the tool as a substitute for your normal file and database backup routines, "it is an additional layer of protection, with the benefit of package-level granularity." DTSBackup is recommended as a tool to transfer packages between servers, either directly or through a file stage. The current version of the tool fully supports SQL Server 2000 and includes features such as a new DTSBackup file format and direct transfer of packages between SQL Servers. These features prevent loss of layout information that you might encounter when using the DTS object model. The tool also includes limited support for password-protected packages, detailed progress information, and a listing of errors encountered during a transfer.
The download comes as two executable files, one for manual transfer with the improved UI, and a command-line executable that you can install separately. The download also includes full product documentation.
Adam reports, "I downloaded the tool and did a few trial runs, and it looked like exactly what we needed." Adam started by backing up msdb. Then he used the DTSBackup tool to transfer all the required packages to his local SQL Server, and finally performed a transfer to files on disk. The production transfer took only a few minutes. His entire transfer task, which might have taken a full day or more, was completed in a few hours.
"We now use DTSBackup as a method of regularly backing up our DTS packages to files on disk," says Adam. "It has become part of my SQL toolset."
READER:
Adam Thurgar
Sydney, Australia
PRODUCT:
DTSBackup 2000
AVAILABLE AT:
SQLDTS.com
CONTACT:
[email protected]
http://www.sqldts.com
