question

SQLRocker avatar image
0 Votes"
SQLRocker asked Cathyji-msft answered

Running 'restore verifyonly' from a different server

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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLRocker,

but i am looking to see if there is a way to run it from a different non-prod server...

Did you want to run the 'restore verify only' on another server(non-prod server)? Copy the backup files to the non-product server, then run the restore scripts. The situation may not just so simple. Why did you want run the script on non-product server? Could you describe your requirement more clearly to make us better understood your issue.

0 Votes 0 ·
SQLRocker avatar image
0 Votes"
SQLRocker answered Cathyji-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLRocker,

Could you share us the backup and restore scripts? Did you want to verify log files for all database?

0 Votes 0 ·
SQLRocker avatar image
0 Votes"
SQLRocker answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLRocker avatar image
0 Votes"
SQLRocker answered

@TomPhillips-1744 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.