备份和恢复DTS(本地包)的方法.

备份的储存过程:

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]GOCreate procedure s_SavePackages@Pathvarchar(128)as/**/set nocount ondeclare @objPackage intdeclare @PackageName varchar(128)declare @rc intdeclare @ServerName varchar(128)declare @FileName varchar(128)declare@FilePath varchar(128)declare@cmd varchar(2000)select @ServerName = @@ServerName ,@FilePath = @Pathif right(@Path,1) <> '/'beginselect @Path = @Path + '/'end-- create output directory - will fail if already exists but ...select@cmd = 'mkdir ' + @FilePathexec master..xp_cmdshell @cmdcreate table #packages (PackageName varchar(128))insert #packages(PackageName)select distinct namefrommsdb..sysdtspackagesselect@PackageName = ''while @PackageName < (select max(PackageName) from #packages)beginselect@PackageName = min(PackageName) from #packages where PackageName > @PackageNameselect@FileName = @FilePath + @PackageName + '.dts'exec @rc = sp_OACreate 'DTS.Package', @objPackage outputif @rc <> 0beginraiserror('failed to create package rc = %d', 16, -1, @rc)returnendexec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageNameif @rc <> 0beginraiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)returnend-- delete old fileselect @cmd = 'del ' + @FileNameexec master..xp_cmdshell @cmd, no_outputexec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileNameif @rc <> 0beginraiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)returnendexec @rc = sp_OADestroy @objPackageendgo

恢复的储存过程:

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]GOCreate procedure s_LoadPackageToServer@PackageNamevarchar(128) ,@FileNamevarchar(500) ,@Usernamevarchar(100) ,@Passwordvarchar(100)as/*execs_LoadPackageToServer@PackageName = 'mypackage' ,@FileName = 'c:/dtspckgs/mypackage.dts' ,@Username = 'sa' ,@Password = 'pwd'*/declare @objPackage intdeclare @rc intexec @rc = sp_OACreate 'DTS.Package', @objPackage outputif @rc <> 0beginraiserror('failed to create package rc = %d', 16, -1, @rc)returnendexec @rc = sp_OAMethod @objPackage, 'LoadFromStorageFile' , null,@UncFile = @FileName, @password = nullif @rc <> 0beginraiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)returnendexec @rc = sp_OAMethod @objPackage, 'SaveToSQLServerAs' , null,@NewName = @PackageName, @ServerName = @@ServerName, @ServerUserName = @Username, @ServerPassword = @Passwordif @rc <> 0beginraiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)returnendgo