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

SQL SERVER - FIX: ERROR : Msg 3136, Level 16, State 1  - This differential backup cannot be restored because the database has not been restored to the correct earlier state keyboardhelp During my recent visit to customer site for a session on backups, they asked me to find the cause of the error while restoring a differential backup. Though this seemed to be completely an admin related topic and I had gone for some other session, I took the challenge head-on. These are wonderful ways to explore and learn SQL Server better. The error they showed me was:

Msg 3136, Level 16, State 1, Line 39
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 39
RESTORE DATABASE is terminating abnormally.

In this blog post I will try to explain about the error in detail. In the same context, long time back, I did write a blog post on: SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

Over there, I have explained details and co-relation of the various backup type i.e. Full, Differential and Transaction Log backups. I will refrain from rehashing them here again.

Recently, one of my friends asked about if we have differential backup, how we can find the full backup on which differential backup can be restored. If we go back to basics, the differential backup has all the changes in the database made since last full backup was taken.

Let us understand this concept using an example:

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE t1 (i INT)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F1.bak'
GO
INSERT INTO t1 VALUES (1)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D1.bak' WITH DIFFERENTIAL
GO
INSERT INTO t1 VALUES (2)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D2.bak' WITH DIFFERENTIAL
GO
INSERT INTO t1 VALUES (3)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F2.bak'
GO
INSERT INTO t1 VALUES (4)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D3.bak' WITH DIFFERENTIAL
GO

Once the script has been run we have below backups.

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

Looking at the backup chain, it is clear that D3 is valid for F2. On the other hand D1 and D2 are valid and restorable on top of F1. Let us drop the database and try to restore D3 on top of F1.

USE MASTER
GO
DROP DATABASE SQLAuthority
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'E:\temp\F1.bak' WITH NORECOVERY
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'E:\temp\D3.bak' WITH NORECOVERY
GO

Here is the output.

Processed 296 pages for database 'SQLAuthority', file 'SQLAuthority' on file 1.
Processed 6 pages for database 'SQLAuthority', file 'SQLAuthority_log' on file 1.
RESTORE DATABASE successfully processed 302 pages in 0.213 seconds (11.076 MB/sec).
Msg 3136, Level 16, State 1, Line 43
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 43
RESTORE DATABASE is terminating abnormally. 

This means that first restore was successful and next one has error which means that this is not a valid differential backup to be restored. How would we figure out the correct sequence of restore? Well, there are multiple ways.

1. Have a look at SQL Server ErrorLog where we have successful backup messages. Here is what we saw in ERRORLOG while running above backups.

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

As highlighted above, we can find the full back up LSN from the message of differential backup.

2. Have a look at Standard Reports to find previous backup events.

SQL SERVER – SSMS: Backup and Restore Events Report

3. Run below query on the server from where backup was taken.

SQL SERVER – Get Database Backup History for a Single Database

Hope fully this blog demystifies and tells you usefulness of the messages in ERRORLOG and logging capability of SQL Server. Do let me know if you have ever encountered these errors in your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized
Next Post
SQL SERVER – Take the Quiz for a chance to win a Quadcopter Drone

Related Posts

6 Comments. Leave new

  • Is there any way to trick SQL into letting you restore a differential without the Full? I’m trying to recover from a disaster, and there is no good Full backup, but we do have a really big differential which we hope could be used to recover what data there is. But of course get that error when we try to restore it without the Full first. Is there anyway to trick SQL into letting your restore it anyway?

    Reply
    • Nope. Differential backup has changes since last full backup. If you don’t have base backup, how can SQL restore apply the differential.

      Reply
      • we are geeting same error, it’s not happening fix.
        Senario: Two sql server on availability group. Db01, Db02
        Full backup form secondary server: Db02
        Differentials backup from primary server: Db01

        restore time:
        — this is full backup
        RESTORE DATABASE dbnew01
        FROM Disk=’G:\AdventureWorks2014_backup_2016_02_08_134030_9954289.bak’
        WITH NORECOVERY
        GO
        — this is differentials backup
        RESTORE DATABASE dbnew01
        FROM DISK=’G:\AdventureWorks2014_Full_2016_02_08_20_10_00.bak’
        WITH RECOVERY
        GO

        after executing this script we are getting below error:

        Processed 23752 pages for database ‘dbnew01’, file ‘AdventureWorks2014_Data’ on file 1.
        Processed 1 pages for database ‘dbnew01’, file ‘AdventureWorks2014_Log’ on file 1.
        RESTORE DATABASE successfully processed 23753 pages in 5.018 seconds (36.979 MB/sec).
        Msg 3136, Level 16, State 3, Line 10
        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 10
        RESTORE DATABASE is terminating abnormally.

        Database showing : Restoring….

  • Very Helpful. Thanks

    Reply
  • we are geeting same error, it’s not happening fix.
    Senario: Two sql server on availability group. Db01, Db02
    Full backup form secondary server: Db02
    Differentials backup from primary server: Db01

    restore time:
    — this is full backup
    RESTORE DATABASE dbnew01
    FROM Disk=’G:\AdventureWorks2014_backup_2016_02_08_134030_9954289.bak’
    WITH NORECOVERY
    GO
    — this is differentials backup
    RESTORE DATABASE dbnew01
    FROM DISK=’G:\AdventureWorks2014_Full_2016_02_08_20_10_00.bak’
    WITH RECOVERY
    GO

    after executing this script we are getting below error:

    Processed 23752 pages for database ‘dbnew01’, file ‘AdventureWorks2014_Data’ on file 1.
    Processed 1 pages for database ‘dbnew01’, file ‘AdventureWorks2014_Log’ on file 1.
    RESTORE DATABASE successfully processed 23753 pages in 5.018 seconds (36.979 MB/sec).
    Msg 3136, Level 16, State 3, Line 10
    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 10
    RESTORE DATABASE is terminating abnormally.

    Database showing : Restoring….

    Reply
  • ReymondPerez
    May 11, 2016 1:38 pm

    Hi Guys,

    Nice post. Is there a way to restore D3 on top of F1.

    Reply

Leave a Reply

Menu