Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Posts
    10

    Exclamation Unanswered: DTS packages backup ?

    Hi,

    On our dev machine there are some DTS packages in the DataTransformationServices\LocalPackages. Our dev machine & DB is backed up every day (.bak file and backup of data files)
    We had a crash and had to restore the DB's, now all DTS packages are gone !

    We're are they saved ? Can I get them back somehow ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DTS packages stored on the server are in msdb.

    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    DTS packages will be saved in MSDB database and if you're not maintained any MSDB backup then assume they are gone.

    Its better and advisable to maintain regular backups for system databases too.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Aug 2003
    Posts
    10
    Thanks for the info,
    but ofcourse they didn't backup the msdb...

  5. #5
    Join Date
    Aug 2003
    Posts
    10
    aha, I found a backup of the data files,
    how can I know get it back,
    I tried a detach/attach of the msdb db but he refuses to detach it ?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You probably want to read up on Restoring the model, msdb, and distribution Databases before you start. There are some gotchas that you need to consider.

    You'll need to use SQL Enterprise Mangler, or BACKUP DATABASE to make a backup, then move that to the new box and restore it.

    -PatP

  7. #7
    Join Date
    Aug 2003
    Posts
    10
    I did that, now I got my jobs back, but no dts packages ?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hmmm... Sorry, no clue. It has never failed me.

    -PatP

  9. #9
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Then consider there is no chance to retrieve the DTS packages, consider this as a lesson and maintain regular backups for SYSTEM Databases too.

    By any chance are the packages saved to a .VBscript files.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  10. #10
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Lightbulb DTS packages backup with DTSBackup 2000 tools

    To make a backup of all kinds of DTS packages or to transfer DTS packages between servers, please use the DTS Backup 2000 tool. This a very handy tool and it works very good; I use it myself very often.

    To get this tool please visit following URL: http://www.sqldts.com/default.aspx?202

    I hope this helps to prevent new problems in the future.

    Greetz,
    DePrins

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check the contents of sysdtspackages in the newly restored MSDB.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    this is antother reason that i always save these things as com files
    you can place them in a centrally stored shared dir and back them up every night.

    on a side note
    you should create a job that backs up your master model and msdb dbs and schedule it for every night.
    it's a whole lot better then running rebuildm.exe

  13. #13
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can also do this by saving them as structured files. They're a little easier to handle and explain to the non-technical savvy people.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    "com file" = "com structured storage file"

  15. #15
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Thought you were referring to the visual basic file option. Oh well, who cares. Big thing is to make sure you save them.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •