备份的储存过程:
Save all DTS packages on server to files
Author Nigel Rivett
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. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[s_SavePackages] GO Create procedure s_SavePackages @Path varchar(128) as /* */ set nocount on declare @objPackage int declare @PackageName varchar(128) declare @rc int declare @ServerName varchar(128) declare @FileName varchar(128) declare @FilePath varchar(128) declare @cmd 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 ... select @cmd = 'mkdir ' + @FilePath exec master..xp_cmdshell @cmd create table #packages (PackageName varchar(128)) insert #packages (PackageName) select distinct name from msdb..sysdtspackages select @PackageName = '' while @PackageName < (select max(PackageName) from #packages) begin select @PackageName = min(PackageName) from #packages where PackageName > @PackageName select @FileName = @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
恢复的储存过程:
This will load the dts package from structured storage file @FileName and save to sql server (msdb) as @PackageName. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_LoadPackageToServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[s_LoadPackageToServer] GO Create procedure s_LoadPackageToServer @PackageName varchar(128) , @FileName varchar(500) , @Username varchar(100) , @Password varchar(100) as /* exec s_LoadPackageToServer @PackageName = 'mypackage' , @FileName = 'c:/dtspckgs/mypackage.dts' , @Username = 'sa' , @Password = 'pwd' */ declare @objPackage int declare @rc int 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, 'LoadFromStorageFile' , null, @UncFile = @FileName, @password = null if @rc <> 0 begin raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end exec @rc = sp_OAMethod @objPackage, 'SaveToSQLServerAs' , null, @NewName = @PackageName, @ServerName = @@ServerName, @ServerUserName = @Username, @ServerPassword = @Password if @rc <> 0 begin raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end go