Our DBA team doesn't verify the backups using the below TSQL
(which can easily be done post-backup and takes almost no time, so I don't get why not):
RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\LOCATION'
They've had issues in the past, so even though we'd think they'd learn from it, they haven't. I created a Powershell script to do this because we have about 100+ servers and I just want to run this script against all the backups just to ensure they're valid. The below script runs correctly (in that it doesn't break or throw errors), I am curious if there's a way to get the printed message back in Powershell that we would normally get in SSMS where it states The backup set on file 1 is valid as verification.
$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "SERVER=SERV\INST;Integrated Security=true;DATABASE=master"
$baks = Get-ChildItem "D:\Backups\" -Filter *.BAK
foreach ($bak in $baks)
{
$SqlCon.Open()
$cd = New-Object System.Data.SqlClient.SqlCommand
$cd.Connection = $SqlCon
$cd.CommandText = "RESTORE VERIFYONLY FROM DISK = @f"
$cd.Parameters.Add("@f", $bak.FullName)
$cd.ExecuteNonQuery()
$SqlCon.Close()
}
invoke-sqlcmd -verbose
?Invoke-SqlCmd ... -Verbose
is also nice if you want to redirect the output for later use instead of just printing it to the host immediately.