What are the challenges you face when working across database platforms? Take the survey

Fail my job please

martinmartin Posts: 22
edited November 1, 2005 9:06PM in SQL Backup Previous Versions

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.



  • Options
    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  
     else begin  
     	insert into #jobresults (message) values ('Backup succeeded: ' + @databasename)  
     drop table #resultstring  
     if @failflag = 1 begin  
     RAISERROR ('Backup failed to complete successfully', 16, 1)  

Sign In or Register to comment.