Backup fails and I'm not sure why

st8floorsupst8floorsup Posts: 27
edited April 2, 2008 9:45AM in SQL Backup Previous Versions
I have the backup script below that never completes if the backup fails for a database and I'm not sure why. It calls this process for
all of my databases but the job stops after the first retry if the backup fails.

Begin
SET @SQL = 'Declare @exitcode int, @sqlerrorcode int, @ErrorMsg varchar(200)'
SET @SQL = @SQL + ' Exec master..sqlbackup N' + '''' + '-SQL "BACKUP DATABASE TO DISK = ' + ''''+ '''' + @Path + @Name +'-'+@cDate+ '_full.sqb' + '''' + '''' + ' WITH NAME = ' + '''' + '''' + @Name + ' Full backup' + '''' + '''' + ', DESCRIPTION = ' + '''' + '''' + 'SQLBackup Full Backup' + '''' + '''' + ', INIT' + ', MAILTO_ONERROR = ' + '''' + '''' + @e_mail_on_error + '''' + ''''
If @ERASEFILES = 'Y'
SET @SQL = @SQL + ', ERASEFILES_ATSTART = ' + convert(varchar(5),@ERASEFILES_ATSTART)
SET @SQL = @SQL + ', COMPRESSION = ' + @COMPRESSION+ '"' + ''''
SET @SQL = @SQL + ',@exitcode OUTPUT, @sqlerrorcode OUTPUT'
SET @SQL = @SQL + ' If @exitcode <> 0
BEGIN
Set @ErrorMsg = ''SQL Backup Failed! exitcode=''+ convert(varchar(20),@exitcode) + '' sqlerrorcode='' +convert(varchar(20),@sqlerrorcode)
RAISERROR(@ErrorMsg ,16,1)
END'

Set @MaxRetries = 10
Set @RetryCounter = 0
WHILE (@RetryCounter < @MaxRetries)
Begin
--Print (@SQL)
SELECT @error_value = 0
Exec (@SQL)
SELECT @ERROR

set @PrintStr = 'ECHO ' + 'BACKUP DATABASE ' + ' @Error_value= ' +convert(varchar(10),isnull(@error_value,9999))+ CASE WHEN (@overwrite = 1) THEN ' > ' ELSE ' >> ' END + RTRIM(@backupfile)
EXEC master..xp_cmdshell @PrintStr

If @error_value = 0
Set @RetryCounter = @MaxRetries
Else
Begin
WAITFOR DELAY '00:30:00'
Set @RetryCounter = @RetryCounter + 1
End
End

set @PrintStr = 'ECHO RetryCounter= ' + convert(varchar(10),@RetryCounter)+ CASE WHEN (@overwrite = 1) THEN ' > ' ELSE ' >> ' END + RTRIM(@backupfile)
EXEC master..xp_cmdshell @PrintStr
End

Here is the text file I am writing to:
BACKUP DATABASE [master] @Error_value= 0
RetryCounter= 10
BACKUP DATABASE [msdb] @Error_value= 0
RetryCounter= 10
BACKUP DATABASE [AdventureWorks] @Error_value= 0
RetryCounter= 10
BACKUP DATABASE [content_store] @Error_value= 0
RetryCounter= 10
BACKUP DATABASE [dm_staging] @Error_value= 50000
BACKUP DATABASE [dm_staging] @Error_value= 50000

Here is the last sql job log:
SQL Backup Failed! exitcode=790 sqlerrorcode=0 [SQLSTATE 42000] (Error 50000). The step failed.

Here is the redgate backup logs:
SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [dm_staging] TO DISK = '\\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb' WITH NAME = 'dm_staging Full backup', DESCRIPTION = 'SQLBackup Full Backup', INIT, MAILTO_ONERROR = '[email protected]', ERASEFILES_ATSTART = 4, COMPRESSION = 1 "

ERRORS AND WARNINGS


4/2/2008 2:10:20 AM: Backing up dm_staging (full database) to:
4/2/2008 2:10:20 AM: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb

4/2/2008 2:10:26 AM: BACKUP DATABASE [dm_staging] TO VIRTUAL_DEVICE = 'SQLBACKUP_BEAA7228-F2FF-4AB5-80F0-A0FF89446D87' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'dm_staging Full backup', DESCRIPTION = N'SQLBackup Full Backup'

4/2/2008 2:12:52 AM: Thread 0 error:
Error 620: Error writing to backup file(s).
4/2/2008 2:12:52 AM: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb
Warning: System error 64 (The specified network name is no longer available)
4/2/2008 2:12:52 AM:
Processed 654440 pages for database 'dm_staging', file 'dm_staging_Data' on file 1.
Processed 1 pages for database 'dm_staging', file 'dm_staging_Log' on file 1.
BACKUP DATABASE successfully processed 654441 pages in 139.656 seconds (38.388 MB/sec).
4/2/2008 2:14:01 AM: Warning 150: MAILTO error: SMTP host name not defined.

SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [dm_staging] TO DISK = '\\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb' WITH NAME = 'dm_staging Full backup', DESCRIPTION = 'SQLBackup Full Backup', INIT, MAILTO_ONERROR = '[email protected]', ERASEFILES_ATSTART = 4, COMPRESSION = 1 "

ERRORS AND WARNINGS


4/2/2008 1:36:20 AM: Backing up dm_staging (full database) to:
4/2/2008 1:36:20 AM: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb

4/2/2008 1:36:24 AM: Deleting old backup file: \\networkstore\SQLBackup\servername\dm_staging-20080329_010251_full.sqb
4/2/2008 1:36:28 AM: BACKUP DATABASE [dm_staging] TO VIRTUAL_DEVICE = 'SQLBACKUP_D89B222B-FB3A-4961-AF56-720579432417' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'dm_staging Full backup', DESCRIPTION = N'SQLBackup Full Backup'

4/2/2008 1:39:19 AM: Thread 0 error:
Error 620: Error writing to backup file(s).
4/2/2008 1:39:19 AM: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb
Warning: System error 64 (The specified network name is no longer available)
4/2/2008 1:39:19 AM:
Processed 654440 pages for database 'dm_staging', file 'dm_staging_Data' on file 1.
Processed 1 pages for database 'dm_staging', file 'dm_staging_Log' on file 1.
BACKUP DATABASE successfully processed 654441 pages in 167.043 seconds (32.094 MB/sec).
4/2/2008 1:40:20 AM: Warning 150: MAILTO error: SMTP host name not defined.

SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [dm_staging] TO DISK = '\\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb' WITH NAME = 'dm_staging Full backup', DESCRIPTION = 'SQLBackup Full Backup', INIT, MAILTO_ONERROR = '[email protected]', ERASEFILES_ATSTART = 4, COMPRESSION = 1 "

ERRORS AND WARNINGS


4/2/2008 1:01:44 AM: Backing up dm_staging (full database) to:
4/2/2008 1:01:44 AM: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb

4/2/2008 1:01:45 AM: BACKUP DATABASE [dm_staging] TO VIRTUAL_DEVICE = 'SQLBACKUP_A08D94AA-DE2A-4F36-BE77-0706C967BDCC' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'dm_staging Full backup', DESCRIPTION = N'SQLBackup Full Backup'

4/2/2008 1:04:37 AM: Thread 0 error:
Error 620: Error writing to backup file(s).
4/2/2008 1:04:37 AM: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: \\networkstore\SQLBackup\servername\dm_staging-20080402_010144_full.sqb
Warning: System error 64 (The specified network name is no longer available)
4/2/2008 1:04:37 AM:
Processed 654440 pages for database 'dm_staging', file 'dm_staging_Data' on file 1.
Processed 7 pages for database 'dm_staging', file 'dm_staging_Log' on file 1.
BACKUP DATABASE successfully processed 654447 pages in 171.644 seconds (31.234 MB/sec).
4/2/2008 1:06:20 AM: Warning 150: MAILTO error: SMTP host name not defined.

SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [content_store] TO DISK = '\\networkstore\SQLBackup\servername\content_store-20080402_010056_full.sqb' WITH NAME = 'content_store Full backup', DESCRIPTION = 'SQLBackup Full Backup', INIT, MAILTO_ONERROR = '[email protected]', ERASEFILES_ATSTART = 4, COMPRESSION = 1 "

PROCESSES COMPLETED SUCCESSFULLY

4/2/2008 1:01:24 AM: Backing up content_store (full database) to:
4/2/2008 1:01:24 AM: \\networkstore\SQLBackup\servername\content_store-20080402_010056_full.sqb

4/2/2008 1:01:25 AM: BACKUP DATABASE [content_store] TO VIRTUAL_DEVICE = 'SQLBACKUP_41B9B1B5-FA05-44C0-BC45-A200B0EDA351' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'content_store Full backup', DESCRIPTION = N'SQLBackup Full Backup'

4/2/2008 1:01:44 AM: Database size : 237.625 MB
4/2/2008 1:01:44 AM: Compressed data size: 5.292 MB
4/2/2008 1:01:44 AM: Compression rate : 97.77%

Processed 5088 pages for database 'content_store', file 'content_store' on file 1.
Processed 2 pages for database 'content_store', file 'content_store_log' on file 1.
BACKUP DATABASE successfully processed 5090 pages in 14.021 seconds (2.973 MB/sec).
4/2/2008 1:01:44 AM: SQL Backup process ended.

If I don't call "RaiseError" then the script continues to the next database and the job doesn't report an error. I'd like to have the
script retry the backup if it fails. I upgraded to 5.3 from 5.0 to see if that would help but it didn't.

Any ideas on why this is not working?
Sign In or Register to comment.