SQL Server Performance

URGENT ! DTS Package backup!

Discussion in 'SQL Server DTS-Related Questions' started by manu_dba, Jun 27, 2007.

  1. manu_dba New Member

    hi sql gurus.....

    I need your guidance please....

    actually by mistake i have deleted three very critical
    DTS packages....

    can you guide me how I can ge them back!

    is it possible by restore msdb can i get all dts packages
    from last night backup

    Can you please guide me that practically how i can save the
    DTS seperately so that If i mess with them
    how i can get that back to previous stage.

    Please guide me sql gurus as soon as possible
  2. danny123 New Member

    I know how to take the backup of the dts packages( Script below) and in case one delete teh dts package you can just open the package from dts and save it with any name you want.<br />but sorry about how to restore from msdb if you dont have backup.I never did that.<br /><br />Create Procedure usp_DTSBackup<br />@PathVARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />AS<br />/*<br />This will save all dts packages on the server to storage files.<br />It uses a trusted connect to access the package - just change the LoadFromSQLServer call to use a sql server connection.<br /><br />*/<br /><br />Set NoCount ON<br /><br />Declare @objPackage INT<br />Declare @PackageName VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />Declare @rc INT<br />Declare @ServerName VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />Declare @FileName VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />Declare@FilePath VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />Declare@cmd VARCHAR(2000)<br /><br />Select @ServerName = @@ServerName ,<br />@FilePath = @Path<br /><br />If Right(@Path,1) &lt;&gt; ''<br />Begin<br />Select @Path = @Path + ''<br />End<br /><br />-- create output directory - will fail if already exists but ...<br />Select@cmd = 'mkdir ' + @FilePath<br />Exec master..xp_cmdshell @cmd<br /><br /><br />Create Table #packages (PackageName VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br />Insert #packages<br />(PackageName)<br />Select Distinct name<br />Frommsdb..sysdtspackages<br /><br />Select@PackageName = ''<br />While @PackageName &lt; (Select MAX(PackageName) From #packages)<br />Begin<br />Select@PackageName = MIN(PackageName) From #packages Where PackageName &gt; @PackageName<br /><br />Select@FileName = @FilePath + @PackageName + '.dts'<br /><br />Exec @rc = sp_OACreate 'DTS.Package', @objPackage OUTPUT<br />If @rc &lt;&gt; 0<br />Begin<br />RaisError('failed to create package rc = %d', 16, -1, @rc)<br />Return<br />End<br /><br />Exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,<br />@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName<br />If @rc &lt;&gt; 0<br />Begin<br />RaisError('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)<br />Return<br />End<br /><br />-- delete old file<br />Select @cmd = 'del ' + @FileName<br />Exec master..xp_cmdshell @cmd, no_output<br /><br />Exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName<br />If @rc &lt;&gt; 0<br />Begin<br />RaisError('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)<br />Return<br />End<br /><br />Exec @rc = sp_OADestroy @objPackage<br />End<br /><br />GO<br />
  3. satya Moderator

    yes you can get the packages from teh MSDB provided you have valid backup, referhttp://www.dbarecovery.com/restoremsdb.html in this case. Use Danny's script to save it to file or make it a practice when the file is saved within the DTS designer.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. manu_dba New Member

    Thanks a lot danny and satya...

    but can you please tell me one thing that...suppose if I
    delete or saved the package by mistake...and i
    want to changes back....

    how i can use your script or msdb latest full backup
    to get DTS Package.

    will restoring the msdb by force on the existing one
    be the solution of gettingf the DTS package to old state.

    Sorry for asking it again in other way but
    please suggest and clear this confusion ....

  5. satya Moderator

    DTS maintains the version, so you can use to execute that version.

    I believe you need a process on change management of such packages, say if you have modified any package then ensure to keep it saved to a file with version number.

    If you restore from thebackup then whatever state that DTSpackage was during the backup of MSDB will be present.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. MohammedU New Member

    quote:Originally posted by manu_dba

    Thanks a lot danny and satya...

    but can you please tell me one thing that...suppose if I
    delete or saved the package by mistake...and i
    want to changes back....

    how i can use your script or msdb latest full backup
    to get DTS Package.

    will restoring the msdb by force on the existing one
    be the solution of gettingf the DTS package to old state.

    Sorry for asking it again in other way but
    please suggest and clear this confusion ....


    Don't restore MSDB on the same server where youd deleted the packages... you can restore msdb onto another system and copy deleted packages....
    And also you need Change management system as Satya mentioned...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] restoring on the same machine will be disasterous, you use restore as MSDB1 in this cae.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>

Share This Page