/*
Author:	J.Meijerink (jeroenm@lostboys.nl)
Date:	02-24-1999

Purpose:
Makes a backup of a defined database and copies the backupfile to another location. Optionally the backupfile can be zipped wich will reduce the filesize with about 80%.
The filename is build from the currentdate + description. For instance: "Feb_24_1999MasterBackup.zip"
Example calls:
"BackupDB "Master", "<description>" ,"C:\temp\Masterbackup.BAK", "c:\temp", 1 		Master will be backed up and zipped to C:\temp
"BackupDB "Master", "<description>" ,"C:\temp\Masterbackup.BAK", "\\Server1\Backup", 0 	Master will be backed up and copied to a servershare

For the zip-part I use a commandline zipper from PkWare wich can be found at http://www.pkware.com. Put the following line in the batchfile (or customize) and
change the pathname in the batchfile and in this procedure to match your locations of  Pkzip25.exe and the batchfile.
D:\Pkzip\Pkzip25.exe -add -silent -path=root  %1 %2
Replace "-add" with "-move" to get rid of the big backupfile
*/

CREATE PROCEDURE BackupDB
	@Db			VARCHAR(30) ,
	@Description		VARCHAR(50) ,		-- Backuplabel
	@LocalDisk		VARCHAR(50) ,		-- Location and filename for the backupfile
	@DestDisk		VARCHAR(50), 		-- Location where the file will be copied to
	@Zip			BIT		= 0	-- 1 = Zip file after backup. 
AS
SET NOCOUNT ON

DECLARE @cmdline		VARCHAR(100)
DECLARE @zipfile		VARCHAR(75)

/* Backup database*/
BACKUP DATABASE @Db 
TO  DISK = @Localdisk 

/* Options */
WITH  INIT ,  		--overwrites existing backupfile
NAME = @Description, 
NOSKIP , 	
STATS = 10, 
DESCRIPTION = @Description, NOFORMAT 

IF @Zip = 1
	BEGIN	
	/* Zip backupfile to destinationsdisk */
		SELECT @Zipfile = @DestDisk + "\" +  LEFT(REPLACE(GETDATE(),' ','_'),11) + @Description
		SELECT @cmdline = "D:\SQL\Zipbackups.cmd " + @Zipfile + ' ' + @Localdisk		-- Change the location of your batchfile in this line
		EXEC master..xp_cmdshell @cmdline, NO_OUTPUT					-- You may want to remove the NO_OUTPUT for testing
	END
ELSE
	BEGIN
	/* Copy file */
		SELECT @cmdline = "COPY " + @Localdisk + " " + @DestDisk
		EXEC master..xp_cmdshell @cmdline, NO_OUTPUT
	END

SET NOCOUNT OFF