Have you ever encountered issues during disaster recovery where the backup chain is broken? In this blog post, we will discuss a common error message that occurs when trying to restore a file from a differential backup in SQL Server.

Let’s start by understanding the concept of differential backups. A differential backup in SQL Server captures only the changes made to the database since the last full backup. This allows for faster backups and restores, as only the modified data needs to be processed.

However, when restoring a file from a differential backup, it is important to ensure that the database has been restored to the correct earlier state. If the database has not been restored to the correct state, you may encounter the following error message:

Msg 3178, Level 16, State 1, Line 26
File SQLAuthority is not in the correct state to have this differential backup applied to it.
Msg 3119, Level 16, State 1, Line 26
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 26
RESTORE DATABASE is terminating abnormally.

To create a scenario where this error occurs, we can follow these steps:

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

The above code creates a series of backups, including a full backup, a differential backup, a copy-only full backup, and another differential backup.

Now, if we try to restore the differential backup on top of the copy-only full backup, we will encounter the error mentioned earlier. To reproduce the error, we can use the following code:

USE [master]
RESTORE DATABASE [SQLAuthority_1] FROM  
DISK = N'SQLAuth_Full_02_cp.bak' 
WITH  
MOVE N'SQLAuthority' TO N'E:\DATA\SQLAuthority_1.mdf',  
MOVE N'SQLAuthority_log' TO N'E:\DATA\SQLAuthority_1_log.ldf',  
NOUNLOAD,  STATS = 5, NORECOVERY
GO 

RESTORE DATABASE [SQLAuthority_1]  FILE = 'SQLAuthority'
FROM  DISK = N'SQLAuth_Diff_02.bak'
WITH RECOVERY,  NOUNLOAD,  STATS = 10
GO

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 command will work as we are restoring diff_02 after performing the 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'E:\DATA\SQLAuthority_1.mdf',  
MOVE N'SQLAuthority_log' TO N'E:\DATA\SQLAuthority_1_log.ldf',  
NOUNLOAD,  STATS = 5, NORECOVERY
GO 

RESTORE DATABASE [SQLAuthority_1]   FILE = 'SQLAuthority'
FROM  DISK = N'SQLAuth_Diff_02.bak' 
WITH RECOVERY,  NOUNLOAD,  STATS = 10
GO

By following these steps, you can successfully restore a file from a differential backup without encountering the error message.

It is important to understand the backup and restore process in SQL Server to ensure a smooth disaster recovery. Always make sure to restore the differential backup on top of the correct full backup to avoid any issues.

Thank you for reading this blog post. We hope you found it helpful in understanding SQL Server differential backup restore concepts. Stay tuned for more informative articles!