Differential backup Restore

SQL Server Administration
1

Hi All,

I am new to SQL Admin. My scenario as follows

Server-A - SQLServer 2014 Standard
Took Full backup of DB XYZ in the morning
Took Differential backup of DB XYZ in the noon.

I want to restore this above backups in Server-B which has SQLServer 2014 Express edition.
- Full backup restore successfully

When I try to restore the differential backup. It gives me error.

Tried :slight_smile:RESTORE DATABASE [XYZ]
FROM DISK = N'E:\test\fullbk\New\Fullbk.bak'
WITH FILE = 1,
MOVE N'Portfolio_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\XYZ.MDF',
MOVE N'Portfolio_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\XYZ.LDF',
REPLACE, NORECOVERY, STATS = 10
GO

for Differential

RESTORE DATABASE XYZ
FROM DISK = N'E:\test\fullbk\New\DiffBkat1PMpm.bak' with RECOVERY

With Regards

2

What is the Error?

Is the database itself larger than 10GB in size (the max size SQL Server Express will allow)?

3

Hi Stephen

  • List item

Error:
Msg 3136, Level 16, State 3, Line 10
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 10
RESTORE DATABASE is terminating abnormally.

  • List item

Size of full backup size is 700 MB.

Note: Even tried by taking full backup with Copy_only option also.

4

Here is the workaround of SQL error Msg 3136 :
https://blog.sqlauthority.com/2017/04/05/sql-server-msg-3136-level-16-differential-backup-cannot-restored-database-not-restored-correct-earlier-state/