Problem description
SQL restores fail with VSS1 error. The Event log shows the following message: The system cannot find the path specified for the checkpoint file.
Cause
SQL creates a checkpoint file on the staging location before it attaches the restored database to the instance. The checkpoint is not found if the path is incorrect.
Traceback
Phoenix Logs:
SyncError: VSS writer has reported failure : [state = 13, result_code = 0], writer details are = [writer class id : {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}, writer name : SqlServerWriter] (#100040001) (Error Code : VSS1)
System Event Logs:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=2000. Thread=12360. Client. Instance=PGSQLDEV. VD=Global\{5CB55310-251E-47F9-A3C2-9C0BCC84EC36}_SQLVDIMemoryName_0.
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: RESTORE DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3136
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
The operating system returned the error '3(The system cannot find the path specified.)' while attempting 'DeleteFile' on 'F:\Backup\RestoreCheckpointDB32.CKP'.
Resolution
Ensure that you fix the writer issues, wherein the writers are in the failed state. Most of the times, system reboots shall fix the failed writers. Run the
VSSADMIN LIST WRITERScommand to check the status.The error mentioned in the Event logs indicates that the location used by the SQL Server to download the database from the cloud for the staging purpose is incorrect. In this case, the
F:\Backup\RestoreCheckpointDB32.CKPlocation is incorrect.Check the database staging location. Right-click the SQL instance and click Properties > Database Settings > DB Default Location > Backups. In our case, the location was set to F:\Backups\ instead of F:\Backup\.
Check for the Read and Write permissions to this path and ensure that the SYSTEM account has full permissions.
Trigger a backup after correcting the path. The backup should now succeed as the SQL server earlier could not find the database that was downloaded from the Phoenix Cloud due to an incorrect path.