SQL SERVER – Msg 3136, Level 16 – This Differential Backup Cannot be Restored Because the Database has not Been Restored to the Correct Earlier State

I was trying to teach copy-only backups to my students and encountered below error related to differential backup.

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

I have written a blog earlier in the same error message.

SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to correct the earlier state

Here are the steps to create the scenario

USE master
GO
CREATE DATABASE SQLAuthority
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth_Full_01.bak' WITH FORMAT
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth_Diff_01.bak' WITH DIFFERENTIAL, FORMAT
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth_Full_02_cp.bak' WITH COPY_ONLY, FORMAT
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth_Diff_02.bak' WITH DIFFERENTIAL, FORMAT
GO

SQL SERVER - Msg 3136, Level 16 - This Differential Backup Cannot be Restored Because the Database has not Been Restored to the Correct Earlier State restore-error-diff-01

The above image shows the series of backup I have taken. You can read more about COPY_ONLY backup in books online. The error would come if I restore Diff_02 on top of Full_02_cp. Below is the code to reproduce the error.

USE [master]
RESTORE DATABASE [SQLAuthority_1] FROM  
DISK = N'SQLAuth_Full_02_cp.bak' 
WITH  FILE = 1,  
MOVE N'SQLAuthority' TO N'G:\SQL_FILES\SQLAuthority_1.mdf',  
MOVE N'SQLAuthority_log' TO N'G:\SQL_FILES\SQLAuthority_1_log.ldf',  
NOUNLOAD,  STATS = 5, NORECOVERY
GO
RESTORE DATABASE [SQLAuthority_1] 
FROM  DISK = N'SQLAuth_Diff_02.bak' WITH  FILE = 1,  
RECOVERY,  NOUNLOAD,  STATS = 10
GO

SOLUTION/WORKAROUND

We need to restore a differential backup on top of regular full backup, not copy_only full backup. Following command would work as we are restoring diff_02 after performing restore of Full_01.

DROP DATABASE [SQLAuthority_1]
GO
USE [master]
RESTORE DATABASE [SQLAuthority_1] FROM  
DISK = N'SQLAuth_Full_01.bak' 
WITH   
MOVE N'SQLAuthority' TO N'G:\SQL_FILES\SQLAuthority_1.mdf',  
MOVE N'SQLAuthority_log' TO N'G:\SQL_FILES\SQLAuthority_1_log.ldf',  
NOUNLOAD,  STATS = 5, NORECOVERY
GO
RESTORE DATABASE [SQLAuthority_1]  
FROM  DISK = N'G:\SQL_FILES\SQLAuth_Diff_02.bak' 
WITH RECOVERY,  NOUNLOAD,  STATS = 10
GO

Have you ever encountered such issues during disaster recovery where chain is broken?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , , ,
Previous Post
SQL SERVER – Msg 3101, Level 16 – Exclusive Access Could not be Obtained Because the Database is in Use
Next Post
SQL SERVER – Script to Audit Login and Role Member Change

Related Posts

1 Comment. Leave new

  • Jickson Sebastian
    April 11, 2017 12:00 am

    the error is expected by design , the copy only backup is intended to ensure that the lsn chain isn’t broken for your regular full and diff backups.

    Reply

Leave a Reply

Menu