Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Copying/backing up DTS Packages

Author  Topic 

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 15:03:34
Does any one know if it is possible to backup or copy DTS packages to local machines or other servers?. I have about 50 or 60 packages I created and would like to archive them in case for diaster recovery.

I know I can open each package and save as Stuctured procedure then open it from SQL Server. That is way too time consuming. Are there any other ways?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 15:11:00
DTS Packages are stored in the msdb database (msdb.dbo.sysdtspackages), which is a system database. System databases should be backed up regularly.

Have a look at this to save all DTS packages to files easily:
http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html



Tara
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 15:44:09
Thanks Tara.

Just wondering if I copy and past that script in Query Analyzer, wheer does it copy the DTS packages to (location)? Also, when needed, how to I copy back the DTS packages from this repository?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:10:15
It saves them to files. I realize you mentioned you already know how to do this, but this script allows you to do it easily.

I would strongly suggest that you backup the msdb database though. It contains much more than just DTS packages.

Tara
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 16:17:30
Thanks again Tara. Do I specif the location of these files? Is there a script that would convert these files back into packages aswell?
Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:22:53
You pass the path into the stored procedure.

I do not know of a script to put them back into SQL Server. Restoring the msdb database would do this for you though.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-23 : 17:46:15
If you save your DTS packages as structured files, it gives you an option on what directory to save them to. You can then copy them to another machine. To open them up on another SQL Server, right-click on Data Transformation Services and select Open Package.

You can then save it on the server you opened it from.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 08:52:51
Thanks guys for all the help. I will need to find out where I set the path and try to see if I can open them on a new server like Derick is suggesting.
Go to Top of Page

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-04-26 : 11:17:22
Another method is to back up sysdtspackages table from msdb database. This has all the information for all dts packages of the server.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-26 : 11:41:19
SQLCode, did you read Tara's first reply ?



Damian
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 12:00:41
Tara:

Have you been sucessful at running the script that you pointed me to? I have tried playing with it, but have had no luck in creating the dts files.

If you have any additional information that might be helpful in getting the script to run, I would appreciate it. Right now, it sucessfully executes, but no files are produced. I did try specifying a path [Set @path='D:\backup'] but that folder doesn't even get created or filled.

Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 12:22:21
I have never run Nigel's script before. I haven't yet had a need to backup the DTS packages separately from the msdb database.

And you probably have to create the folder yourself first.

Tara
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 13:49:53
Tara:

I am really sorry to be going back and forth on this...

The script says that if you folder already exists, it will fail, so I did try running the part that just creates the folder and sucess it does to the right path, but if I run as a whole, nothing happens.

You mention, you haven't had the need to backup individual DTS packages, becuase you back up the system db. I tried creating this aswell. The thing is I am not too sure what I should be seeing on the other end. When I walk throught he maintenance plan wizard, I set all the parameters and the job is created, right? I right lick on the job and run, it creates a bak file in the default location. The file is 350 Mb, I tried opening the file in notepad to see what is in it, and its unreadable. Once you have a the bak file, and you want to restore it, do I just walk through the wizard to restore it to the approriate server?

I have a dev servere and a local server, lets say I want to backup dev and restore it to my local server. Now just by backing up msdb, Model and Master, I will have all the packages, triggers, views etc...?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 14:04:13
You can not open backup files using notepad or any other utility for that matter. They must be restored in order to get to the info. And yes you can use the wizard to restore it. I would not recommend restoring the msdb database onto a different server than which it was backed up though. You should only do this for user databases, meaning not master, msdb, tempdb, or model.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-26 : 14:17:36
If you have a local SQL Server installation you don't mind trashing though, you can restore the msdb database. This will get you access to the dts packages and jobs. That's the only one you need to restore.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 16:05:39
Ladies and Gents... does this ever get any easier?....

I am able to backup the msdb database or for that matter all system databases, but SQL Server only allows me to restore to the same server, is there a way to tell SQL SERVER to restore to another server (local).

If I try to restore from the local servere, it finds no backups on the local and I can't point to the development server to pick up the backup.

Arrrggghhhh! Frustrating!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 16:09:38
Move the file to the destination server. Run your RESTORE from Query Analyzer.

Tara
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 16:24:44
I can move it to the destination, no problem, is this the script I would use in Query Analyzer:

USE msdb
GO
RESTORE DATABASE msdb
FROM 'c:\temp\msdbdata.bak'
GO

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 16:28:01
If the MDF and LDF files need to be moved somewhere different than what the source db has, then you'll need to add WITH MOVE option. But if they are identical, then yes that is the command but add WITH REPLACE and don't run USE msdb. It's not needed.

Tara
Go to Top of Page

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-26 : 16:38:01
What is MDF and LDF?

I will try tomorrow, I have had enough for today! Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 16:40:43
Those are the extensions of the database files.

Tara
Go to Top of Page
    Next Page

- Advertisement -