SQL SERVER – FIX : Msg 3178, Level 16 – File SQLAuthority is Not in the Correct State to have This Differential Backup Applied to it

Few days back I wrote a blog about error messages during differential backup restore. SQL SERVER – Msg 3136, Level 16 – This Differential Backup Cannot be Restored Because the Database has not Been Restored to the Correct Earlier State

This blog is a variation of the same, but instead of restoring complete backup, we were trying to restore a file from differential backup. Here is the 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.

Here are the steps to create the scenario

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

SQL SERVER - FIX : Msg 3178, Level 16 - File SQLAuthority is Not in the Correct State to have This Differential Backup Applied to it restore-error-file-01

The above image shows the series of backup I have taken. You can read more about COPY_ONLY backup in books online. The error would come if I restore Diff_02 on top of Full_02_cp. Below is the code to reproduce the error.

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

SOLUTION/WORKAROUND

We need to restore differential backup on top of regular full backup, not copy_only full backup. Below command would work as we are restoring diff_02 after performing 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

Have you ever encountered such issues during disaster recovery where chain is broken?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , , ,
Previous Post
SQL SERVER – FIX : Msg 8115, Level 16, Arithmetic Overflow Error Converting IDENTITY to Data Type INT
Next Post
SQL SERVER – Mirroring Error: Connection handshake failed. Could not send a handshake message because the connection was closed by peer. State 26

Related Posts

Leave a Reply

Menu