| 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.htmlTara |
 |
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
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! |
|
|
|
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 |
|
|
|
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 msdbGORESTORE DATABASE msdb FROM 'c:\temp\msdbdata.bak'GO |
|
|
|
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 |
|
|
|
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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-26 : 16:40:43
|
| Those are the extensions of the database files.Tara |
|
|
|
Next Page
|
Copying/backing up DTS Packages