We are using the SQL Server Backup solution from Ola Hallengren in our environment for SQL Servers. Today, there were 2 database which were in SUSPECT mode and a backup was not possible for them. A job is scheduled to take backup of these databases (two of which were in Suspect mode) and one another database. The backup of the online DB got completed since it was first in the Alphabetical order and for the other two, it failed. However, the job did not fail and showed the job as successful! The job should have failed if it fails to take the backup of any database.
Message from the Log:
Date and time: 2018-03-18 03:12:55 Database: [ABC] Status: SUSPECT Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Differential base LSN: 5334862000009909000054 Last log backup LSN: N/A Date and time: 2018-03-18 03:12:55 Database: [CDE] Status: SUSPECT Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Differential base LSN: 4543119000022597000098 Last log backup LSN: N/A Date and time: 2018-03-18 03:12:55
One more scenario, where the DB backup fails but it does not report it as [FAILED] in the Log unlike to when it logs when the DB backup is successful - Outcome: Succeeded.
How can we get the information of failed backups from the logs? There is no Tag or Keyword which can be searched for when looking for failed backups in the Log. There are only incomplete command statements which can tell that the backup has failed and for that you have to search the whole log and any kind of reporting is not possible where we can use the concept of keyword searching in log content for failures.
It would be great if you can please let us know what is the solution to these.