Switching off "verify backup integrity" toggle in maintenance plan for full backup

SQL Server Administration
1

Hi everyone,

I would appreciate your comments on this issue. I want to switch off the "verify backup integrity" toggle in the maintenance plan (MP from here on in) as I am consistently getting an error of the following nature every week when I run the main backup process:

The error is:
"VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The step that are occuring in the Maintenance Plan are:

  1. Check database integrity task
  2. Update statistics task (but see below)
  3. Backup database integrity task
  4. If current backup runs successfully then remove the previous backup

Concerning #2 - we found that this was taking prohibitively long so I isolated that task and identified the long running indexes.
I now update the statistics the the night before the backup process and take a suitable sample for example:

UPDATE STATISTICS [dbo].[TBL_LONG_RUNNING] WITH SAMPLE 50 PERCENT

Anyway because of the error mentioned above the previous backup is not removed. Despite the fact that the new backup is successfully created. I get emailed with an alert and its now annoying me.

Hence, I want to switch off the "verify backup integrity" task and get some sounding on doing just that. For example, I read somewhere that to be sure the backup should be restored periodically and a DBCC CHECKDB run to make sure it is sound.

Therefore if you could make any suggestions/comments/advice I would greatly appreciate it.
Thanks in advance, J.

2

  1. I would be very concerned if UPDATE STATISTICS was taking a long time. As I have never had any problems with this I would start investigations using Google.

  2. Maintenance plans, while better than nothing, have a number of 'features' so most people do not use them. Personally I use Ola Hallengren's Maintenance Solution which is very flexible and well tested:
    https://ola.hallengren.com/

I understand a number of people use the free version of Minion Backup:
https://www.minionware.net/products/backup/

ps I do not bother with 'verify backup integrity' as I do a restore and then run checkdb on the restore. (ie A backup is useless if it is corrupted.)

3

Thank you for your feedback. Greatly appreciated. J