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)
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 .
The Test-DbaLastBackup cmdlet allows you to specify VerifyOnly and defaults to checking all databases for the specified instance.
Test-DbaLastBackup -SqlServer sql2016 -VerifyOnly