SQL Server Performance Forum – Threads Archive

URGENT ! DTS Package backup!

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

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. but sorry about how to restore from msdb if you dont have backup.I never did that. Create Procedure usp_DTSBackup @PathVARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS /* This will save all dts packages on the server to storage files. It uses a trusted connect to access the package – just change the LoadFromSQLServer call to use a sql server connection. */ Set NoCount ON Declare @objPackage INT Declare @PackageName VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Declare @rc INT Declare @ServerName VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Declare @FileName VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> [email protected] VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> [email protected] VARCHAR(2000) Select @ServerName = @@ServerName , @FilePath = @Path If Right(@Path,1) <> ” Begin Select @Path = @Path + ” End – create output directory – will fail if already exists but … [email protected] = ‘mkdir ‘ + @FilePath Exec master..xp_cmdshell @cmd Create Table #packages (PackageName VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) Insert #packages (PackageName) Select Distinct name Frommsdb..sysdtspackages [email protected] = ” While @PackageName < (Select MAX(PackageName) From #packages) Begin [email protected] = MIN(PackageName) From #packages Where PackageName > @PackageName [email protected] = @FilePath + @PackageName + ‘.dts' Exec @rc = sp_OACreate ‘DTS.Package’, @objPackage OUTPUT If @rc <> 0 Begin RaisError(‘failed to create package rc = %d’, 16, -1, @rc) Return End Exec @rc = sp_OAMethod @objPackage, ‘LoadFromSQLServer’ , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName If @rc <> 0 Begin RaisError(‘failed to load package rc = %d, package = %s’, 16, -1, @rc, @PackageName) Return End – delete old file Select @cmd = ‘del ‘ + @FileName Exec master..xp_cmdshell @cmd, no_output Exec @rc = sp_OAMethod @objPackage, ‘SaveToStorageFile’, null, @FileName If @rc <> 0 Begin RaisError(‘failed to save package rc = %d, package = %s’, 16, -1, @rc, @PackageName) Return End Exec @rc = sp_OADestroy @objPackage End GO

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.

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 ….

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.

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.

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] restoring on the same machine will be disasterous, you use restore as MSDB1 in this cae. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

]]>