Alert when the restore verify only fails on the latest backup of all the database

Posted on

Question :

I need a way to validate the backup files of all the database on weekly basis.
A script which should run Restore Verify Only on the latest backup file of all the database and incase the backupset in not usable an alert must be sent.

Backups are being taken using Ola scripts. I don’t want to verify on daily basis. It should check on weekly basis. I’m looking for the script which should be called.

I think inserting the backup location and database name in temporary table and sending the rows of this table as input to restore verify Only will work but need some hep to pass the column value as a parameter to restore verify only query

DECLARE @dayswithout int = 1
CREATE TABLE #tablenames (id int NOT NULL IDENTITY (1, 1),name nvarchar
(500),location varchar (500))

INSERT INTO #tablenames (name,location)
select bs.database_name,bmf.physical_device_name
from msdb.dbo.backupset bs
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = 'd'
AND (CONVERT(datetime, bs.backup_finish_date, 102) >= GETDATE() - @dayswithout)

Now as i have the backup location for the last 24 hours database backup i want to pass this to RESTORE VERIFYONLY one by one and if that fails should send alert .

Answer :

An option would be to download the dbatools powershell package and utilize the Test-DbaLastBackup cmdlet in a Sql Agent job. If the job fails, send an alert.

The Test-DbaLastBackup cmdlet allows you to specify VerifyOnly and defaults to checking all databases for the specified instance.

Test-DbaLastBackup -SqlServer sql2016 -VerifyOnly 

Leave a Reply

Your email address will not be published. Required fields are marked *