Running 'restore verifyonly' from a different server

Question

So we use ola's solution for backups. Currently running into an issue with t-log backups where if sql fails on the 'restore verifyonly' part - it creates a dump and sql freezes during that time which we are trying to avoid, msft is looking into the issue.

I can disable verify option, but i am looking to see if there is a way to run it from a different non-prod server...

Ola creates different folders for the dbs, each having the 'log' folder for .trn's.

so i am just thinking if anyone else has done this before, somehow i want to give the main backup folder location to the solution , which should then go in and get the .trn filenames and maybe do a restore verifyonly .... i know its a bit complicated and could be messy, but just wanted to see if someone has implemented something similar, thanks.

Answer 1

Well, the backup trn files are already on a network share, so there is no need for them to be copied over to non-prod server.

I have already explained why i am looking into this.

Answer 2

@Ola Hallengren - Not sure if i got the right ola, but please let me know about above if any ideas, thanks.

Answer 3

A system dump is not normal. Please make sure you have the current patches installed for your version.
https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level

Answer 4

@Tom Phillips Yes, that was the 1st thing checked, its already updated.

@CathyJi-MSFT All that i am trying to see is if someone out there has an automated process to do 'restore verifyonly' from a different server. The .trn's are already going to a network share.

Answer 5

Hi @SQLRocker ,

> All that i am trying to see is if someone out there has an automated process to do 'restore verifyonly' from a different server. The .trn's are already going to a network share.

Try below T-SQL. Please change the database names and the file location of back up files in below T-SQL.

USE [master]  

declare  @DATABASES table(ID INT,DBName  varchar(100))  
insert into @DATABASES   values  

(1, 'database01'),  
(2, 'database02'),  
(3, 'database03'),  
(4, 'database04'),  
(5, 'databae05'),  
(6, 'database06'),  
(7, 'database07'),  
(8, 'database08')  


DECLARE @DBName nvarchar(50)  
DECLARE @PATH nvarchar(max)  
DECLARE @logPATH nvarchar(max)  
declare @SQL nvarchar(max)  
DECLARE @count INT  
SET @count = 1  

WHILE (@COUNT < 30)  
BEGIN  

SELECT @DBName = (SELECT DBName  
                FROM @DATABASES  
                WHERE ID = @count)  

SET @PATH = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\' + @DBName + '.bak'  
SET @logPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\' + @DBName + '.trn'  



SET @SQL=N'RESTORE VERIFYONLY FROM DISK =  '''+@logPATH+''''  

PRINT @SQL  
--exec @SQL  
EXECUTE sp_executesql @sql  


SET @COUNT = @COUNT + 1   
SET @DBName=''  
set @SQL=''  
END  
GO  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.