I currently use Ola Hallengren's SQL Maintenance scripts in my environment, including backups, since they support SQL Backup 6. We have a particular SQL server running 978 Databases. Since about a month ago, the backup jobs are 'failing'. By failing I mean that a couple of databases out of the 978 apparently are not backing up correctly or are not verified because SQL Backup is spitting out an error. I'm trying to understand what it means. Here's the log output from one of the 'failed' differentials.
DateTime: 2011-03-15 01:04:27
Database: [Mosaic_Prod_475765_87_CE]
Status: ONLINE
Mirroring role: None
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Differential base LSN: 32000000804400037
Last log backup LSN: NULL
DateTime: 2011-03-15 01:04:27
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir N'\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
DateTime: 2011-03-15 01:04:27
DateTime: 2011-03-15 01:04:27
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master..sqlbackup N'-SQL "BACKUP DATABASE [Mosaic_Prod_475765_87_CE] TO DISK = N''\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb'' WITH CHECKSUM, DIFFERENTIAL, COMPRESSION = 3"' IF @ReturnCode <> 0 RAISERROR('Error performing SQLBackup backup.', 16, 1)
SQL Backup v6.4.0.56
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Backing up Mosaic_Prod_475765_87_CE (differential database) to:
\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb
Backup data size : 3.688 MB
Compressed data size: 51.000 KB
Compression rate : 98.65%
Processed 40 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE' on file 1.
Processed 8 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_data' on file 1.
Processed 8 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_indexes' on file 1.
Processed 1 pages for database 'Mosaic_Prod_475765_87_CE', file 'sysft_CE' on file 1.
Processed 1 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 57 pages in 0.289 seconds (1.591 MB/sec).
SQL Backup process ended.
name value
---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode 0
sqlerrorcode 0
filename001 \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb
Outcome: Succeeded
Duration: 00:00:09
DateTime: 2011-03-15 01:04:36
DateTime: 2011-03-15 01:04:36
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master..sqlbackup N'-SQL "RESTORE VERIFYONLY FROM DISK = N''\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb''"' IF @ReturnCode <> 0 RAISERROR('Error verifying SQLBackup backup.', 16, 1)
SQL Backup v6.4.0.56
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Verifying file:
\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb
The backup set on file 1 is valid.
SQL Backup process ended.
Warning 110: Failed to save primary log file: Cannot create file "T:\Program Files\Red Gate\SQL Backup\Log\(local)\20110315 010436 01.log". The process cannot access the file because it is being used by another process.
SQL Backup exit code: 110
name value
---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode 110
sqlerrorcode 0
filename001 \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb
Msg 50000, Level 16, State 1, Server DWRTSQL01, Line 1
Error verifying SQLBackup backup.
Outcome: Failed
Duration: 00:00:07
DateTime: 2011-03-15 01:04:43
Now, it seems to me that the backup completed, but the verification failed, but not necessarily because the backup file was bad but because some other external issue. It is stating the backup exit code is 110. What does that code indicates?
Hi, thank you for your post into the forum.
DateTime: 2011-03-15 01:04:27 Database: [Mosaic_Prod_475765_87_CE] Status: ONLINE Mirroring role: None Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: SIMPLE Differential base LSN: 32000000804400037 Last log backup LSN: NULL DateTime: 2011-03-15 01:04:27 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir N'\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) Outcome: Succeeded Duration: 00:00:00 DateTime: 2011-03-15 01:04:27 DateTime: 2011-03-15 01:04:27 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master..sqlbackup N'-SQL "BACKUP DATABASE [Mosaic_Prod_475765_87_CE] TO DISK = N''\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb'' WITH CHECKSUM, DIFFERENTIAL, COMPRESSION = 3"' IF @ReturnCode <> 0 RAISERROR('Error performing SQLBackup backup.', 16, 1) SQL Backup v6.4.0.56 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Backing up Mosaic_Prod_475765_87_CE (differential database) to: \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb Backup data size : 3.688 MB Compressed data size: 51.000 KB Compression rate : 98.65% Processed 40 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE' on file 1. Processed 8 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_data' on file 1. Processed 8 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_indexes' on file 1. Processed 1 pages for database 'Mosaic_Prod_475765_87_CE', file 'sysft_CE' on file 1. Processed 1 pages for database 'Mosaic_Prod_475765_87_CE', file 'Mosaic_Prod_475765_87_CE_log' on file 1. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 57 pages in 0.289 seconds (1.591 MB/sec). SQL Backup process ended. name value ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- exitcode 0 sqlerrorcode 0 filename001 \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb Outcome: Succeeded Duration: 00:00:09 DateTime: 2011-03-15 01:04:36 DateTime: 2011-03-15 01:04:36 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = master..sqlbackup N'-SQL "RESTORE VERIFYONLY FROM DISK = N''\\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb''"' IF @ReturnCode <> 0 RAISERROR('Error verifying SQLBackup backup.', 16, 1) SQL Backup v6.4.0.56 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Verifying file: \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb The backup set on file 1 is valid. SQL Backup process ended. Warning 110: Failed to save primary log file: Cannot create file "T:\Program Files\Red Gate\SQL Backup\Log\(local)\20110315 010436 01.log". The process cannot access the file because it is being used by another process. SQL Backup exit code: 110 name value ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- exitcode 110 sqlerrorcode 0 filename001 \\dwcifs01\sqlbackups01\Prod\DWRTSQL01\Mosaic_Prod_475765_87_CE\DIFF\DWRTSQL01_Mosaic_Prod_475765_87_CE_DIFF_20110315_010427.sqb Msg 50000, Level 16, State 1, Server DWRTSQL01, Line 1 Error verifying SQLBackup backup. Outcome: Failed Duration: 00:00:07 DateTime: 2011-03-15 01:04:43Now, it seems to me that the backup completed, but the verification failed, but not necessarily because the backup file was bad but because some other external issue. It is stating the backup exit code is 110. What does that code indicates?