waqqas31
asked on
Can I limit the execution time of "RESTORE HEADERONLY" in SQL Server?
I am running SQL Server 2017 Developer Edition with CU8 installed with SSMS 17.9.1.
I am running a script that goes through all the backup files on a network share and collects the header data for each file using the "RESTORE HEADERONLY" command.
I have run into some files that might be corrupt, and they take over an hour to return an error, whereas a successful run of the command is completed in 1-2 seconds.
Is there a way I can limit the execution time of the "RESTORE HEADERONLY..." command so that it runs for a maximum of 60 seconds?
I am reading files from network shares on other servers.
I have already tried limiting execution time to 60 seconds with this:
But the RESTORE HEADERONLY command still kept running for a long time on the suspected corrupt file.
Any help would be greatly appreciated!
I am running a script that goes through all the backup files on a network share and collects the header data for each file using the "RESTORE HEADERONLY" command.
I have run into some files that might be corrupt, and they take over an hour to return an error, whereas a successful run of the command is completed in 1-2 seconds.
Is there a way I can limit the execution time of the "RESTORE HEADERONLY..." command so that it runs for a maximum of 60 seconds?
I am reading files from network shares on other servers.
I have already tried limiting execution time to 60 seconds with this:
use master
go
exec sp_configure 'remote query timeout', 60;
go
reconfigure;
goBut the RESTORE HEADERONLY command still kept running for a long time on the suspected corrupt file.
Any help would be greatly appreciated!
ASKER
@Scott, sadly, including "WITH STOP_ON_ERROR" did not yield any different results.
One detail I would like to add is that the server does "throw" an 18210 error (we have set up triggers for it). The description of that error (in case you didn't know) is:
One detail I would like to add is that the server does "throw" an 18210 error (we have set up triggers for it). The description of that error (in case you didn't know) is:
BackupIoRequest::ReportIoError: read failure on backup device '<filename>'. Operating system error 87(The parameter is incorrect.).
Hmm, seems to be that SQL thinks the file is "bad".
Is the file compressed? Or put out by a 3rd party vendor. For example, I know RedGate uses their own backup formats that SQL can't natively read.
Is the file compressed? Or put out by a 3rd party vendor. For example, I know RedGate uses their own backup formats that SQL can't natively read.
ASKER
@Scott,
The file is compressed using SQL Server's own built-in backup compression (BACKUP... WITH COMPRESSION)
The file is compressed using SQL Server's own built-in backup compression (BACKUP... WITH COMPRESSION)
Run a
RESTORE VERIFYONLY
against one (or more) them, just to be sure the files aren't corrupted.
RESTORE VERIFYONLY
against one (or more) them, just to be sure the files aren't corrupted.
ASKER
@Scott,
Of course, I also suspect that the files might be corrupt, and a RESTORE VERIFYONLY would help conclude that, but:
a) It takes a much longer time to run
b) I just want to collect the header information for the 'good' files and carry on if the file is not readable (in as little time as possible.)
The point of my script is not to establish the integrity of the files, rather it is to locate the full backup needed for each differential backup file.
My backup process includes a verification check at the end, so that diligence has already been done. (And if I did that, you may ask, why are these files having header issues? Right now your guess is as good as mine!)
Of course, I also suspect that the files might be corrupt, and a RESTORE VERIFYONLY would help conclude that, but:
a) It takes a much longer time to run
b) I just want to collect the header information for the 'good' files and carry on if the file is not readable (in as little time as possible.)
The point of my script is not to establish the integrity of the files, rather it is to locate the full backup needed for each differential backup file.
My backup process includes a verification check at the end, so that diligence has already been done. (And if I did that, you may ask, why are these files having header issues? Right now your guess is as good as mine!)
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
RESTORE HEADERONLY
FROM ...
WITH STOP_ON_ERROR;
I'd be curious to hear the results if you have time to try it on a file or two.