Fail my job please

martin

Hi.

I have had a look in the FAQ and on this message board, but could not find any trace of a question similare to mine. If this has been answered before, please accept my applogies.

We are currently looking at using red-gates SQLBackup, and as you would guess, we are testing it out.

We have something like 80 - 90 servers and approx 1000 databases.

The problem is that we do not want to receive MAILTO_ONERROR for each failure but rather use our existing system where a script runs through all our jobs (and steps) to evaluate when it ran last time, was is successful, when will it run next, did any single step fail, etc, etc. All this information is pumped out to a web server so we can view the report through our browser.

The problem is that I have not been able to get the scheduled backup job to report a failure even if the script fails to work.

I create the job in Enterprise manager the normal way, and use the following script in the job step

master..sqlbackup '-SQL "BACKUP DATABASE [pubs]  TO DISK = ''C:\arafat\BACKUP\FULL_(local)_pubs_20051102 104035.sqb'' WITH NAME = ''Database (pubs), 2/11/2005 10:40:30 AM'', DESCRIPTION = ''Backup on 2/11/2005 10:40:30 AM  Database: pubs  Instance: (local)  Server: WC016962'', COMPRESSION = 1" -E'

As an example: If the path does not exsist, the job does not fail, but reports as successful even tough the backup never happened.

Am I doing something wrong here?

If anyone could help me out here, I would be happy.

Regards,
Martin

martin

Asked before I was thinking!! I fond this (modified now) in the documentation

 
 declare @datestamp varchar(30)  
 declare @databasename varchar(200)  
 declare @backuppath varchar(500)  
 declare @filename varchar(500)  
 declare @backupstring varchar(1000)  
 declare @failcount int  
 declare @failflag int  
 
  
 create table #resultstring (message varchar (2500))  
 
 set @failflag = 0  
 set @backuppath = 'E:\DBBACKUP\SQLBackup\'  
 set @databasename  = 'pubs'
 
 set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)  
 set @filename = @backuppath + @databasename + '_db_(full)_' + @datestamp + '.sqb'  
 set @backupstring = '-SQL "BACKUP DATABASE [' + @databasename + '] TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full'', ERASEFILES_ATSTART = 1, COMPRESSION = 1" -E'  
 
 insert into #resultstring  
 exec master..sqlbackup @backupstring  
 select @failcount = count(*) from #resultstring  
 where patindex('%error%', message) > 0  
 
 if @failcount > 0 begin  
	 set @failflag = 1  
 end  
 else begin  
 	insert into #jobresults (message) values ('Backup succeeded: ' + @databasename)  
 end  

 drop table #resultstring  

 if @failflag = 1 begin  
 RAISERROR ('Backup failed to complete successfully', 16, 1)  
 end

Regards,
Martin