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:

Set the default backup location to a path that i know does not exist (V:\Backups)

David Fowler

David is a DBA with over 14 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK.

Adrian Buckman

After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 4 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies