July 26, 2005 at 4:16 am
Hi - I currently use a job to backup my db (MSDE 2000) to the same drive the database is on (this is a dedicated server I lease - with no tape backup - and I run my web applications and MSDE 2000 together on it).
So my DB gets backed up each night, with the datestamp as a filename identifier. The Job then sends an email to me using the XP_CDO dll to let me know if it was successful, or if it failed. This all works well.
However, is it possible from within my job, to invoke winzip, and zip the newly created backup file, and attach it to my email which is sent from the job?
I just want to be able to have a sort of off-site backup, just in case.
Thanks for any help,
Mark
July 26, 2005 at 10:53 pm
Yep. You could do it from the command shell if you wanted to.
declare @cmd varchar(255)
set @cmd = 'C:\temp\pkzip.exe whatever.zip *.*'
EXEC master..xp_cmdshell @cmd, no_output
July 27, 2005 at 4:59 am
Hi - thank you.
I'm not familiar with this - are theer examples which demonstrate hjow to implement anywhere?
Thanks, Mark
July 27, 2005 at 6:19 am
because of email size constraints, i would suggest having the job FTp the file to another location instead.
many email servers have a 4 meg or 10 meg limit on them, which might doink your email plans in the future as your db or log grows.
there is a script named "FTP files using SQL stored procedure" here on SCC:
http://www.sqlservercentral.com/scripts/contributions/638.asp
Lowell
July 27, 2005 at 8:45 am
I use this procedure to perform backups and zip the backup files. I use winzip 9 with the command line interface installed. This procedure only uses a few of the options that winzip will give you but it can get you started.
create procedure usp_backup_and_zip
@dbName sysname = null
,@BackupLocation varchar(500) = null
,@ZipProgram varchar(500) = null
,@RemoveBackup tinyint = null
,@Encrypt tinyint = null
,@Password varchar(50) = null
as
-- Author: Eric Mueller
-- Date Written: 4/6/2005
--
-- this procedure will accept the database name to be
-- backed up. It will
-- backup the selected database and zip it. Optionally it will encrypt the backup file
-- as well using the encryption methods within WINZIP. The naming convention used will
-- be ... ServerName_dbName_ZIP_ccyymmddhhmmss.zip
-- Return Codes: 0 - success
-- 1 - failure
-- Accepts the following parameters:
-- @dbName - (required) This is the database to be backed up
-- @BackupLocation - (required) This will be both the location of the backup and
-- the resting place for the zip file
-- @ZipProgram - (optional) path and .exe for the zip program, will default to
-- C:\WINZIP\WZZIP if not otherwise supplied
-- @RemoveBackup - (optional) default to 0, if 1 then remove the backup file when done
-- @Encrypt - (optional) default to 0, this is the same as no encryption; if a password
-- is supplied this will default to 1.
-- 0 - no encryption
-- 1 - standard Zip 2.0 encryption (password required)
-- 2 - AES128 encryption (password required)
-- 3 - AES256 encryption (password required)
-- @Password - (optional) password for zip file NOTE: THIS MUST BE RETAINED IN A SAFE
-- PLACE, IF IT IS USED THERE IS NO WAY OF RECOVERING THE PASSWORD.
set nocount on
-------------------------------------------------------------------------------
-- Declare and initialize Variables
-------------------------------------------------------------------------------
declare @sql varchar (4000)
declare @rtnError int -- This will be the error code for returning from this procedure
declare @processError int -- This will be used for capturing errors from called routines
declare @ServerName varchar (128)
declare @CommandString varchar (500)
declare @BackupName varchar (500)
declare @ZipName varchar (500)
declare @BackupDate char(12)
set @rtnError = 0
set @processError = 0
set @ServerName = @@ServerName
set @BackupDate = convert(varchar(12) ,getdate(),112) + right( '00' + Cast(datepart(hh, getdate()) as varchar), 2)
+ right( '00' + Cast(datepart(mi, getdate()) as varchar), 2)
-------------------------------------------------------------------------------
-- Validate and assign input values
-------------------------------------------------------------------------------
-- validate database name
if @dbname is null
begin
-- database name is required
set @rtnError = 1
goto End_of_Logic
end
else
begin
-- validate database name
if @dbName not in (select [name] from master..sysdatabases)
begin
set @rtnError = 1
goto End_of_Logic
end
end
-- Verify that the path is entered
if @BackupLocation is null
begin
-- path information is required
set @rtnError = 1
goto End_of_Logic
end
-- Set a value for the Zip program location
if @ZipProgram is null
set @ZipProgram = 'C:\WINZIP\WZZIP'
-- Set a value for the remove backup flag
if @RemoveBackup is null
set @RemoveBackup = 0
-- validate password and encryption and build command parameters
if @Encrypt is null
set @Encrypt = 0
if @Encrypt > 3
begin
-- Invalid encryption request
set @rtnError = 1
goto End_of_Logic
end
if @Encrypt <> 0
begin
if (@Password is null or @Password = '')
begin
-- password is required if encrytpion is requested
set @rtnError = 1
goto End_of_Logic
end
else
begin
-- encyrption is requested and password is ok
set @CommandString = '-s' + @Password + case @Encrypt
when 2 then ' -ycAES128'
when 3 then ' -ycAES256'
else ''
end
end
end
-- if backup directory does not exist then create it
set @sql = 'if not exist ' + @BackupLocation + ' (md ' + @BackupLocation + ')'
exec @processError = master..xp_cmdshell @sql,no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
-- Set backup name and zip name
set @BackupName = @BackupLocation + '\' + @ServerName + '_' + @dbName + '_' + @BackupDate + '.bak'
set @ZipName = @BackupLocation + '\' + 'ZIP' + '_' + @ServerName + '_' + @dbName + '_' + @BackupDate + '.zip'
-- Backup Database
backup database @dbName to disk = @BackupName
-- Build and execute ZIP logic
set @sql = @ZipProgram + ' ' + @ZipName + ' ' + @BackupName
if @CommandString is not null
set @sql = @sql + ' ' + @CommandString
print @sql
exec @processError = master..xp_cmdshell @sql,no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
-- Remove backup file if requested
if @RemoveBackup = 1
begin
set @sql = 'del ' + @BackupName
exec @ProcessError = master..xp_cmdshell @sql, no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
end
-- End_of_Logic
End_of_Logic:
return @rtnError
GO
Hope this helps
Eric
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply