Restores using Invalid Backup Default Locations

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:

RestoreCheckpointDB<DatabaseID>.CKP

e.g

RestoreCheckpointDB19.CKP

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:

1. Set the default backup location to a path that i know does not exist (V:\Backups)
2. Restore Database (Competed successfully)
3. Error log shows the following errors:
4. Lets correct the Backup location: Right click the instance and click Properties > Database Settings
5. Change the Backup path at the bottom of the page from V:Backups to C:Backups
6. Run the restore again
7. Error log is now happy

Thanks for reading.