Recently I was looking through the error log on one of my test machines and I spotted some unusual errors:
SQL ERROR: 3634 – The operating system returned the error ‘3(The system cannot find the path specified.)’ while attempting ‘DeleteFile’
SQL ERROR: 18272 – During restore restart, an I/O error occurred on checkpoint file (operating system error (null)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.
At first I assumed that I may have tried restoring a database to a location that did not exist but this was not the case, the actual issue was with SQL Server’s Default Backup Location.
SQL Server uses the Default backup location to create a Database Checkpoint file for which is uses to track the progress of a given restore, this file is named with the following convention:
The file’s main purpose is for use when Restarting interrupted restores, when the Database restore completes the file is deleted from the directory.
So in my example I had a situation where SQL could not create the Checkpoint file (due to the backup location being incorrect) nor able to delete the file , this did not affect my restore this completed successfully but if I had wanted to restart from an interrupt then this would not have been possible and I would have had to reissue the original statement.
Here is a demonstration of the issue:
- Set the default backup location to a path that i know does not exist (V:\Backups)
- Restore Database (Competed successfully)
- Error log shows the following errors:
- Lets correct the Backup location: Right click the instance and click Properties > Database Settings
- Change the Backup path at the bottom of the page from V:Backups to C:Backups
- Run the restore again
- Error log is now happy
Thanks for reading.