Scheduling backups - verification,AlwaysOn and completion
Published 22 November 2018
On step 6 of the wizard, specify the level of verification of your backup. You can also set up email notification.
You can select checks to run on the backup file after the backup has been created. These checks are most effective when used together.
- Select CHECKSUM to validate any existing page checksums in the database and generate a backup checksum. The backup checksum is validated on restore. This option is not available with SQL Server 2000 and earlier.
- Select VERIFY to run RESTORE VERIFYONLY on the backup after it has been created. This checks that the backup file is complete and can be read. If CHECKSUM is also selected, the backup checksum and page checksums are validated as part of the check.
These checks do not verify the structure of the data in the backed up database; it is possible for these checks to succeed and for the backup to be unusable because the data is corrupt.
Backup verification reminders
The most reliable test of a backup is to:
- Restore the database from the backup.
- Run a database integrity check (DBCC CHECKDB) on the restored database to check the data is not corrupt.
You can use the Schedule Restore Jobs wizard to schedule regular restores of a database from the most recent backup and run a database integrity check as part of the restore job. The results of the backup verification are then displayed in the Activity History.
To manage verification of your backups, you can create a reminder to schedule a regular restore job for your databases. Select the Create reminder check box to add a reminder for each database included in the backup job to the Reminders tab on the user interface. For each database, the details of the backup schedule are listed. When you launch the Schedule Restore Jobs wizard from the Reminders tab, the wizard is populated with details from the backup job.
The Create reminder option is only available if you selected the backup type Full on step 2 of the wizard. If you also selected Differential or Transaction log backups, details of those scheduled backup jobs are also included in each reminder.
The Create reminder option is not available if you selected file or filegroup backups, or when editing a backup job.
For more information, see Backup verification.
Availability group settings
You can select support for AlwaysOn Availability group.These options are most effective when used together and placed on all replicas for the Availability Group.
- Select CHECK_PREFERRED_AG_REPLICA to ignore backing up on non-preferred replicas. No backup will be taken on non-preferred replicas, however the backup job will succeed.
- Select SECONDARY_REPLICA_COPY_ONLY to apply the COPY_ONLY flag for full backups on a secondary replica..
If you want to receive an email with a copy of the completion log, select the Send email notification check box, and enter the recipient's email address. This option is available only if you have entered your email settings. For more information about setting up email notification, see Email settings.
To send the log to multiple email addresses, type each address separated with a semi-colon (;). For example:
By default, email notifications are sent only when there are errors during the backup process (Error only). If you want to receive an email when an error or warning occurs, select Error or warning; to always receive an email on completion of the backup process (on success or failure), select Any outcome.