During my recent visit to a customer site, I was asked to troubleshoot an error while restoring a differential backup in SQL Server. Although this topic may seem more relevant to database administrators, it is important for all SQL Server users to understand the concept of differential backups and how they relate to the overall backup and restore process.

The error message that was encountered 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 order to understand this error, let’s first revisit the basics of differential backups. A differential backup contains all the changes made to a database since the last full backup was taken. This means that in order to restore a differential backup, the corresponding full backup must be restored first.

Let’s consider an example to illustrate this concept:

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

Based on the above script, we have created a backup chain consisting of full backups (F1 and F2) and differential backups (D1, D2, and D3). Looking at the backup chain, it is clear that D3 is valid for F2, while D1 and D2 are valid and restorable on top of F1.

Now, let’s drop the database and attempt 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

The output of the restore operation will be:

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 error message indicates that the first restore operation was successful, but the second one failed because the differential backup (D3) cannot be restored without first restoring the correct earlier state (F2).

So, how can we determine the correct sequence of restore? Here are a few methods:

1. Check the SQL Server ErrorLog for successful backup messages. The full backup LSN (Log Sequence Number) can be found in the message of the differential backup.
2. Use the Standard Reports in SQL Server Management Studio to find previous backup events.
3. Run a query on the server from where the backup was taken to retrieve the database backup history.

Understanding the messages in the SQL Server ErrorLog and utilizing the logging capabilities of SQL Server can greatly assist in troubleshooting backup and restore issues.

I hope this blog post has helped demystify the error encountered while restoring a differential backup and has provided useful insights into the backup and restore process in SQL Server. If you have ever encountered similar errors in your environments, feel free to share your experiences.