Sql-server – During restore restart, an I/O error occurred on checkpoint file

backuprestoresql server

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.

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.

Best Answer

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.

Related Solutions

Sql-server – restore could not start database

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):
```
RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
```
In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

Sql-server – Getting File not found error doing point in time database restore in SQL Server 2008

When you open that dialog, SQL Server populates that windows with a lot of backups for the history of backups taken for that database. This history is stored in msdb and is not synchronized with the file system, so it is possible that the backup files themselves were deleted or moved already.

Instead of restoring from that history, select to restore from disk and pick the file(s) yourself.

Restoring a Backup

You need to first restore a full backup. That restore has to happen without recovery.

Then, if you have one, restore the latest differential between the full backup and the point in time you want to restore to. this also has to happen without recovery.

Finally, you need to restore all log backups between that differential (or the full if you didn't have one) and the point in time you want to restore to. Those log backups need to be restored in chronological order, also without recovery. The final log backup you can restore with recovery to bring the database back online.

SQL Server does not force a specific extension for any backup type. Most likely you will have .bak for full backups. Differentials might be either .bak or .dif. For log backups I have seen .trn, .log and others. Some people don't use extensions at all for their backups.

But it is probably a safe guess, that your .trn file is a log backup, so you will need additional backup files to restore that database.

Best Answer

Related Solutions

Sql-server – restore could not start database

Sql-server – Getting File not found error doing point in time database restore in SQL Server 2008

Related Question