no error returned while the backup is not done

mechie

Hi,

we use sql backup on several servers. we configure the job to send an mail for when the backup hasn't been done properly. the mailing etc works fine.

we had a job running which made it's backup properly until it stopped 2 days. although we configured to receive an email with problems we didn't receive an warning. lucky enough we have an extra monitoring tool that alerts us if a database has not been backupped for more than 2 days (which means i already miss 2 backups). i was not happy the sqlbackup didn't do it's backup without us informing of it. also it didn't put the job in failed status.

i did have a look at it and the cause was quickly found when i used the job inside query analyzer:
master..sqlbackup '-SQL "BACKUP DATABASE [database] TO DISK = ''d:\somewhere\database\<AUTO>'' WITH NAME = ''Database(database)'', DESCRIPTION = ''Backup Database: database Server: SOMESERVER'', INIT, PASSWORD = ''<ENCRYPTEDPASSWORD>**removed**==</ENCRYPTEDPASSWORD>'', VERIFY, KEYSIZE = 256, ERASEFILES = 1h, MAILTO_ONERROR = ''hcdba@somedomain.nl'', COMPRESSION = 2"'
(i did edit the databasename, emailaddress and password)
(this job is the only statement and only job step)

the result is:
SQL Backup v4.5.0.704

---

Trial has expired.

(1 row(s) affected)

name value

---

---

exitcode 667
sqlerrorcode 0

(2 row(s) affected)

so i know how to solve it.

now the question:
can you change the behaviour that if a backup had not been made for some reason (and i mean for whatever reason) it wil do 2 things:
1 put the job on failure status
2 mail the information using the supplied email address

also my collegue noticed a different cause where we weren't informed that sql backup didn't do its backupjob either:
we have installed most of our servers in a standard way. which means backups are placed on F:. This server didn't have an F: and the backups should have been done on E:
my collegue noticed that if you configure the backupjob to use a drive that does not exist we dont receive a mail and also no job failure status (the job didn't do it's backup off course)

(i checked the knowledge base and found one article but that was meant for calling from stored procedures)

mechie

for your info i added a generated script to make the job. i removed the encrypted password and renamed the db to dbxx.

also we have a support contract so if you need that i can provide you that information to your email.

Eduard Beijnes, Holland Casino, The Netherlands


-- Script generated on 27-7-2006 12:29


BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Full Backup DBXX') > 0
PRINT N'The job "Full Backup DBXX" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Full Backup DBXX', @owner_login_name = N'sa', @description = N'Full Backup DBXX d.m.v. SQLBackup', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
--
-- i removed the encryption key
--
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Backup', @command = N'master..sqlbackup ''-SQL "BACKUP DATABASE [DBXX] TO DISK = ''''E:\SQLServer\MSSQL\Backup\DBXX\<AUTO>'''' WITH NAME = ''''Database(DBXX)'''', DESCRIPTION = ''''Backup Database: DBXX Server: SOMESERVER'''', INIT, PASSWORD = ''''<ENCRYPTEDPASSWORD></ENCRYPTEDPASSWORD>'''', VERIFY, KEYSIZE = 256, ERASEFILES = 1h, MAILTO_ONERROR = ''''hcdba@somedomain.nl'''', COMPRESSION = 2"''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily', @enabled = 1, @freq_type = 4, @active_start_date = 20060713, @active_start_time = 30000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

mechie

about the job:
we generated the statement using the wizard.
i assume this is meant with:

When using the scheduling wizard and log shipping wizard in SQL Backup version 4 and up, this is not an issue. The necessary parameters are inserted into the backup job step in the SQL Agent job.