Sql-server – SUSPECT Status Of Databases, but Ola Hallengren backup job completed SUCCESSFULLY

backupola-hallengrensql server

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.

enter image description herehttps://i.stack.imgur.com/n8Ww1.png

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.

Best Answer

Your problem was easily reproduced.

I used the code in Creating a SUSPECT Database to create a suspect database on my local instance and then ran Ola's backup script. The script executed without error (as you described). The output did report the database as being suspect, but the script did not error.

Date and time: 2018-03-18 06:20:07
Database: [DemoSuspect]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: SIMPLE
Encrypted: No
Differential base LSN: N/A
Last log backup LSN: N/A

In addition to not backing up suspect databases, there are (at least) Three cases where Ola Hallengren's Maintenance Solution won't backup a database.

A Major takeaway from this post is:

You should always always double-check either the msdb backup history or the master.dbo.CommandLog table to make sure any important backup was taken.

There are a variety of ways to query successful backup using the samples in Script to retrieve SQL Server database backup history and no backups. An example would be to look for any databases that haven't been backed up today.

SELECT d.NAME
    ,bck.last_db_backup_date
FROM sys.databases d
LEFT JOIN (
    SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
        ,msdb.dbo.backupset.database_name
        ,MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    GROUP BY msdb.dbo.backupset.database_name
    ) bck ON bck.database_name = d.NAME
WHERE bck.last_db_backup_date IS NULL
    OR convert(DATE, bck.last_db_backup_date) <> convert(DATE, sysdatetime())

My suggestion would be to add an extra job step to verify that all databases are being backed up and force the job to abend if a problem situation is detected.

Related Question