Differential backup cannot be restored . BACKUP with COPY_ONLY

23 August,2010 by Jack Vamvas

An Operations member of staff approached me with a problem - which I thought might be worth posting .
He was asked to do a RESTORE of a full backup and the related Differential. He executed the RESTORE for the FULL BACKUP
as :


He then attempted to RESTORE the differential , but was confronted with the following message:

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

A quick check of the SQL Server Logs indicated that there was a manual backup taken by a Developer which broke the chain . Looking at the logs below
indicates that the base backup was taken , followed by a differential . The third log entry points to a manual full backup , and the fourth entry is a differential but using 3 as the base backup . You can derive this information from reading the - full backup LSN entry on the Differential backups - and how both differentials refer to different base backups.

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Differential backup cannot be restored . BACKUP with COPY_ONLY

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer