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