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

Fail my job please

martinmartin Posts: 22
edited November 1, 2005 9:06PM in SQL Backup Previous Versions
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

Comments

  • 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  
     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
Sign In or Register to comment.