zip_n_move

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.zip_n_move') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.zip_n_move

GO

CREATE PROCEDURE zip_n_move
     @dbName VARCHAR(30) = ' ',           -- the database name
     @fileName VARCHAR(30) = ' ',         -- file to move less ext
     @source VARCHAR(30) = ' ',           -- source server
     @sourcePassword VARCHAR(30) = ' ',   -- source sa password
     @dumpShare VARCHAR(10) = 'alle',     -- source drive share 
     @dumpFolder VARCHAR(30) = 'backup',  -- source folder
     @target VARCHAR(30) = ' ',           -- target server
     @targetPassword VARCHAR(30) = ' ',   -- target sa password
     @loadShare VARCHAR(10) = 'alle',     -- target drive share
     @loadFolder VARCHAR(30) = 'restore', -- target folder
     @saPassword VARCHAR(30) = ' ',       -- admin sa password
     @debug VARCHAR(5) = 'false'          -- debug 
 AS
DECLARE 
     @dumpPath VARCHAR(50),               -- fully qualified dump path
     @dumpArchive VARCHAR(80),            -- source archive PATH
     @loadPath VARCHAR(50),               -- fully qualified load path 
     @loadArchive VARCHAR(80),            -- target archive PATH 
     @CMD VARCHAR(255),                   -- reusable command holder 
     @CMDstatus INT,                      -- return status of command prompt  
     @drive varchar(5),                   -- logical disk drive
     @myName VARCHAR(30)                  -- name of this function 
  /*
    Evoke a zip utility on the source system
    First get the logical drive name of the share on the source system.
    need this to get pkzip to run on that drive on NT3.51 
  */

SET NOCOUNT ON

SELECT @myName = 'zip_n_move'

IF  (@dbName = ' ') 
 OR (@fileName = ' ')
 OR (@source = ' ')
 OR (@target = ' ')
  BEGIN
    PRINT "[usage: zip_n_move <file name>,         ]"
    PRINT "[                  <source server>,     ]"
    PRINT "[                  <source sa password>,]"
    PRINT "[                  <source share>,      ]"                 
    PRINT "[                  <source folder>,     ]"                    
    PRINT "[                  <target server>,     ]"                     
    PRINT "[                  <target sa password>,]"                           
    PRINT "[                  <target share>,      ]"                      
    PRINT "[                  <target folder>,     ]"                       
    PRINT "[                  <debug?>             ]"
    RETURN 1
  END
/*
IF DATALENGTH(@fileName) > 8
  BEGIN
    SELECT "Short file names required (i.e., 88888888.333)"
    SELECT "Call to zip_n_move has been aborted"
    RETURN 1
  END
*/
SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + 
                   @dumpFolder + '\' + @dbName 
SELECT @dumpArchive = @dumpPath + '\archive'
SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' + 
                   @loadFolder + '\' + @dbName
SELECT @loadArchive = @loadPath + '\archive'

EXEC convert_share_to_drive @dumpShare, 
                            @drive OUTPUT, 
                            @dumpPath,
                            @source,
                            @sourcePassword,
                            @saPassword,
                            @debug
IF @fileName = @dbName
  SELECT @CMD = @drive + '&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' +
                'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' + 
                SUBSTRING(@fileName,1,4) + 'dump.dat -b' + @drive
ELSE
  SELECT @CMD = @drive + '&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' +
                'archive\' + @fileName + '.zip ' + 
                 @fileName + '.dat -b' + @drive
SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" 
SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source +
              ' -dmaster -Q"' + @CMD + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus
IF @fileName = @dbName
  SELECT @CMD = 'copy ' + 
                @dumpArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' +
                @loadArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip '
ELSE
  SELECT @CMD = 'copy ' + 
                @dumpArchive + '\' + @fileName + '.zip ' +
                @loadArchive + '\' + @fileName + '.zip'
If @debug = 'true' select @CMD
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus
convert_share_to_drive @loadShare, 
                            @drive OUTPUT, 
                            @loadPath,
                            @target,
                            @targetPassword,
                            @saPassword,
                            @debug

IF @fileName = @dbName
  SELECT @CMD = @drive + '&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' +
                'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip '
ELSE
  SELECT @CMD = @drive + '&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' +
                'archive\' + @fileName + '.zip'
SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" 
SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target +
              ' -dmaster -Q"' + @CMD + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus
RETURN
GO