Error: I/O error occurred on checkpoint file ...databasename.CKP (4223279)

When running Log Shipping during the restore restart, an I/O error occurred on checkpoint file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupdatabasename.CKP'

Error may include: "I/O error on backup or restore restart-checkpoint file 'C:\Program Files\Microsoft SQL Server\MSSQL\backup\LAW.ckp'. Operating system error 3(The system cannot find the path specified.). The statement is proceeding but is non-restartable."
When you run a restore, a checkpoint file is created. For checkpoint information in regards to a restore, check out the topic in BOL titled "Checkpoints and the Active Portion of the Log".

or the following error may appear:
Error: 18272, Severity: 16, State: 1.
During restore restart, an I/O error occurred on checkpoint file 'G:\MSSQL.1\MSSQL\Backup\databasename.CKP' (operating system error 1784(error not found)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

During these restores, a checkpoint error was logged in the windows system event log.
It doesn't matter that the source is 64-bit and destination is 32-bit, this has been successfully tested.

The directory where checkpoint file would be located may be missing. This error may display when the path for the checkpoint file (file_name.ckp) does not exist on the destination Server on which you are restoring the database.

The problem here is that the restore operation is trying to create a temporary .CKP (checkpoint) file in the path: %:\mssql2000\MSSQL\backup\,
which actually does not exist on this server. Without that directory structure, it is unable to create the checkpoint file (ckp file), which is created by the backup/restore process to track how much has been backed up/ restored so that it can be restarted. The *.CKP file is normally deleted when the restore is complete.

The checkpoint file is a temporary file, deleted when the restore process is completed, and is normally placed in the default backup folder.

See : https://support.microsoft.com/en-us/kb/2022254

See also: https://support.microsoft.com/en-us/kb/2083921

Resolution 1:

Microsoft has published a KB article discussing the checkpoint problem. This is not a LiteSpeed error, but LiteSpeed can trigger it.

Ref: https://support.microsoft.com/en-us/kb/2022254, https://support.microsoft.com/en-us/kb/2083921

Resolution 2:

1. Verify that the folder listed in the error message, does exist on subscriber instance host.
2. If not, create the path and run the restore command again and see if it completes with no errors in the errorlog.
3. Is the database on the listed drive? Databases should be installed on the non -O/S array.
4. The checkpoint is needed when you are using norecovery, which you are in your script. Try running the sp_restore_database stored procedure by specifying the parameters directly rather than through variables.
5. Is this backup from an older version of SQL Server? If so, you will have to get it out of recovery mode, update its compatibility to 90 and then turn it into norecovery or standby mode as needed. All previous logs will not be recoverable.
6. Verify the correct value in the registry (on both nodes if you're on a cluster):
LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\'BackupDirectory'
Change the value of the BackupDirectory Key (path) to point to a valid area on your Network where the checkpoint file can be created. The same operation must be repeated on Node number 2 if you're on a cluster.

If you've made any changes, these registry paths will need to be examined e.g. if you installed SQL Server on the F(data) and G(log) drives but subsequently needed to change the drive lettering to D(Log) and E(Data) as I did, then you'll need to look at the parameters section which is under the same node i.e.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Typical parameters are:
SQLArg0 -dc:\MSSQL7\data\master.mdf
SQLArg1 -ec:\MSSQL7\log\ERRORLOG
SQLArg2 -lc:\MSSQL7\data\masterlog.ldf

You'd also need to modify the paths in the following tables in the master database also --- sysfiles, sysaltfiles, sysdevices, sysdatabases

For additional information regarding Veritas backup, see:
<http://seer.support.veritas.com/docs/266649.htm>
Also see: Seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31901

How to implement checkpoints in packages:
Integration Services supports checkpoints. You can configure a package to use checkpoints and this way make it is possible to restart it from the point of failure, rather than rerun the whole package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When you rerun the failed package, the checkpoint file is used to identify where the package failed previously and restart the package from the point of failure. If the package reruns successfully, the checkpoint file is deleted.

To set the properties related to checkpoints, expand the Checkpoints node in the Properties window.

To configure checkpoints, follow these steps:
Set the SaveCheckpoints property to True to indicate that the package saves checkpoints.
Provide the name and location of the checkpoint file in CheckpointFileName property.
Set the value of the CheckpointUsage property (see Table 16-2).
To identify tasks and containers in the package as restart points, select each task or container, and in the Properties window set its FailPackageOnFailure property to True.

For additional info, see:http://searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1248707,00.html