Comfirming param for failure email on a restore job
egay
Posts: 5
I know you have to manually edit the restore job -- what's the param to use for an email on failture during a restore? Is the the same as the backup? (MAILTO_ONERROR)
The restore job is --
DECLARE @errorCode INT
DECLARE @sqlerrorCode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [WillowQC1] FROM DISK = ''\\wh_reporting\incoming\LOG_WillowQC1_*.sqb'' WITH ERASEFILES = 168h, STANDBY = ''e:\2005-data\MSSQL$SQL2005\Backup\UNDO_WillowQC1.dat'', MOVETO = ''E:\2005-data\MSSQL$SQL2005\TRN_LOG_APPLIED''"', @errorCode OUT, @sqlerrorCode OUT;
IF (@errorCode >= 500) OR (@sqlerrorCode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode)
END
The restore job is --
DECLARE @errorCode INT
DECLARE @sqlerrorCode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [WillowQC1] FROM DISK = ''\\wh_reporting\incoming\LOG_WillowQC1_*.sqb'' WITH ERASEFILES = 168h, STANDBY = ''e:\2005-data\MSSQL$SQL2005\Backup\UNDO_WillowQC1.dat'', MOVETO = ''E:\2005-data\MSSQL$SQL2005\TRN_LOG_APPLIED''"', @errorCode OUT, @sqlerrorCode OUT;
IF (@errorCode >= 500) OR (@sqlerrorCode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode)
END
Comments
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [WillowQC1] FROM DISK = ''\\wh_reporting\incoming\LOG_WillowQC1_*.sqb'' WITH ERASEFILES = 168h, STANDBY = ''e:\2005-data\MSSQL$SQL2005\Backup\UNDO_WillowQC1.dat'', MOVETO = ''E:\2005-data\MSSQL$SQL2005\TRN_LOG_APPLIED'', MAILTO_ONERROR = ''admin@yourco.com'' "', @errorCode OUT, @sqlerrorCode OUT;
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8