3

Short version: Running a RESTORE VERIFYONLY throws one of these errors:

During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB24.CKP' (operating system error 5(Access is denied.)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

Or

During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB11.CKP' (operating system error (null)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

More information:

  • Using Ola's SQL Server Maintenance Solution
  • The file share is on a Dell DR4300 device
  • Has occurred on multiple servers using this device for backup storage
  • Different servers, service accounts, versions of SQL Server have all thrown this error
  • Not consistent or reproducible as far as I can tell (happened Monday & Wednesday but not Tuesday)
  • Subsequent runs of the backup jobs have succeeded without error
  • Time of day does not seem to be relevant, it's occurred at all hours
  • The error is not immediate, it occurs minutes into the execution of the command
  • Default backup directory points to \\Server\Backup\SQLDirectBackup\DatabaseName\
  • Hasn't happened with an actual restore yet... but I'd like to avoid that. Hence the post.

I've never seen this error before, and all my research is coming up empty. It is possible that this has been happening for a long time but I am new to the company and only recently added Severity 16+ alerting so I cannot be sure.

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm stumped.

  • Please check if the account running the RESTORE VERIFYONLY command has read and write privilege to the default backup directory. It is trying to write the checkpoint file. – SqlWorldWide Jul 26 '18 at 18:09
  • It does. I'm backing up all USER_DATABASES on the server with Ola's script. Today, nine databases on the server ran the same command successfully within 45 minutes of the error. Then 12 more afterwards. I'm pretty sure this is related to the backup hardware, but I can't find anything other than these sporadic errors. – Jacob H Jul 26 '18 at 18:28
2

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm stumped.

It's not a "bomb", this means only that if you want to use RESTORE with RESTART option (to complete interrupted RESTORE ) you'll not be able to do it, i.e. checkpoint file cannot be used and the RESTORE will not be able to skip some passes if they woudl be done prior to interruption.

You can read more about checkpoint file here: Restores using Invalid Backup Default Locations

The cause of this error is that your default backup location becomes temporarily inaccessible.

You can just ignore this error or you can change the default backup location with any local path.

  • This makes sense. Will test this weekend. Thanks for the reply. – Jacob H Jul 27 '18 at 18:02
  • Ok so the cause was not the default backup location. As that was set properly. The issue was not 100% clear, but was apparently related to the DR device itself. After rebooting and running the dedupe/maintenance procedure, suddenly we had many more terabytes of space free... and all the random I/O and network errors were gone. – Jacob H Jul 31 '18 at 12:46
  • >>>As that was set properly.<<< My answer was not about "wrong setting". It was about problems with this location, IO problems. By changing it to another location(local disk) you could exclude these problems, at least network problems – sepupic Jul 31 '18 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.