As a SQL Server user, you may have come across various backup and restore scenarios. One such scenario involves differential backups and the error message that can occur when attempting to restore them. In this article, we will explore the error message “Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to correct the earlier state” and discuss the steps to resolve it.

Let’s start by understanding the concept of differential backups. A differential backup is a type of backup that captures only the changes made to a database since the last full backup. It allows for faster backups and restores by reducing the amount of data that needs to be processed.

In the example provided, the error occurs when attempting to restore a differential backup on top of a copy-only full backup. The copy-only backup is a special type of full backup that does not affect the backup chain or interfere with the regular backup schedule. However, when a differential backup is taken after a copy-only backup, it creates a break in the backup chain, resulting in the error message mentioned above.

To resolve this issue, we need to restore the differential backup on top of a regular full backup, not a copy-only full backup. The following steps demonstrate the correct sequence:

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

By restoring the regular full backup (Full_01) before the differential backup (Diff_02), we ensure that the backup chain remains intact and the error is avoided.

It is important to note that encountering such issues during disaster recovery is not uncommon. Understanding the backup and restore process, as well as the different types of backups, can help prevent and resolve such errors.

In conclusion, the error message “Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to correct the earlier state” occurs when attempting to restore a differential backup on top of a copy-only full backup. To resolve this issue, it is necessary to restore the differential backup on top of a regular full backup. By following the correct sequence, you can ensure the integrity of the backup chain and avoid encountering this error.