Backup fails and I'm not sure why
st8floorsup
Posts: 27
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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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?
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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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 = 'sqladmin@medassets.com', 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?
