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

备份也可以通过打开包另存为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]
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