dumpDatabase

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

GO

CREATE PROCEDURE dumpDatabase
     @dbName VARCHAR(30)= ' ',
     @serverName VARCHAR(30) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @dumpDevice Varchar(80) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(30)

/*
  Procedure dumpDatabase truncates the log in a datbase then dumps 
  the database to an init'ed file (no device needed). This means it
  will blow away any existing file of that name. 
*/

SET NOCOUNT ON

SELECT @myName = 'admin.dumpDatabase'
 
IF (@dbName = ' ') OR (@serverName = ' ') or (@dumpDevice = ' ') RETURN 1

/* dump the database's log on the source system */
SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @serverName + 
              ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + 
              ' with TRUNCATE_ONLY' + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus

/* dump the database on the source system */
SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @serverName + 
              ' -dmaster -Q"DUMP DATABASE ' + @dbName + 
              " TO DISK = '" + RTRIM(LTRIM(@dumpDevice)) + 
              "' WITH INIT, RETAINDAYS = 0" + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus

RETURN
GO