In this article, we are going to discuss how to solve the error which can occur during the restoration of a differential backup. Here is the error 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.

To understand why this error has happened and how to avoid this in the future, let’s recreate this error message and discuss the solution.

Recreating – This differential backup cannot be restored because the database has not been restored to the correct earlier state

Let’s create database “Adventureworks” and apply the following backup database scenarios –  Full – Differential – Differential – Full – Differential

CREATE DATABASE Adventureworks
GO
USE Adventureworks
GO
CREATE TABLE Tab (i INT)
GO
BACKUP DATABASE Adventureworks TO DISK = 'D:\Adventureworks_Full_1.bak'
GO
INSERT INTO Tab VALUES (1)
GO
BACKUP DATABASE Adventureworks TO DISK = 'D:\Adventureworks_Diff_1.bak' WITH DIFFERENTIAL
GO
INSERT INTO Tab VALUES (2)
GO
BACKUP DATABASE Adventureworks TO DISK = 'D:\Adventureworks_Diff_2.bak' WITH DIFFERENTIAL
GO
INSERT INTO Tab VALUES (3)
GO
BACKUP DATABASE Adventureworks TO DISK = 'D:\Adventureworks_Full_2.bak'
GO
INSERT INTO Tab VALUES (4)
GO
BACKUP DATABASE Adventureworks TO DISK = 'D:\Adventureworks_Diff_3.bak' WITH DIFFERENTIAL
GO

Now we have the backup chain, where backups with the names Adventureworks_Diff_1 and Adventureworks_Diff_2 are valid for Adventureworks_Full_1 and Adventureworks_Diff_3 valid for Adventureworks_Full_2. It’s time to drop database Adventureworks and then restore Adventureworks_Diff_3 on top of Adventureworks_Fullf_1.

USE MASTER
GO
DROP DATABASE Adventureworks
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_Full_1.bak' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_Diff_3.bak' WITH NORECOVERY
GO

After we will receive the following Error 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.

Solution – This differential backup cannot be restored because the database has not been restored to the correct earlier state

To solve this issue all we need to do is to restore backups without breaking the backup chain. It means that we need to restore the last full backup Adventureworks_Full_2 and then the last differential backup Adventureworks_Diff_3. Check the command below:

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_Full_2.bak' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_Diff_3.bak' WITH RECOVERY
GO

Here what we received

To avoid this error message in the future we highly recommend you use the SqlBak tool. SqlBak can create a scheduled backup, send them to the selected destination place and all restore backups! Here you can find the short tutorial on how to create automatical backups and how to restore them.