Restores using Invalid Backup Default Locations

tick-642162_1280

 

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:2017-08-29 12_59_09-Log File Viewer - LAPTOP-ADRIANB_SQLDEV2
  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:Backups2017-08-29 13_28_48-Server Properties - LAPTOP-ADRIANB_SQLDEV2
  6. Run the restore again
  7. Error log is now happy

 

Thanks for reading.

 

 

One thought on “Restores using Invalid Backup Default Locations

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: