SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored

最新推荐文章于 2025-04-03 22:26:54 发布

转载最新推荐文章于 2025-04-03 22:26:54 发布 · 488 阅读

DB 专栏收录该内容

14 篇文章

订阅专栏

本文解析了SQL Server在尝试还原差异备份时出现的错误Msg 3136，并提供了确定正确还原顺序的方法，包括查看错误日志获取完整备份的LSN等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转自：https://blog.sqlauthority.com/2014/11/10/sql-server-fix-error-msg-3136-level-16-state-1-this-differential-backup-cannot-be-restored-because-the-database-has-not-been-restored-to-the-correct-earlier-state/

During my recent visit to customer site for a session on backups, they asked me to find the cause of the error while restoring a differential backup. Though this seemed to be completely an admin related topic and I had gone for some other session, I took the challenge head-on. These are wonderful ways to explore and learn SQL Server better. The error they showed me was:

在这里插入图片描述
注解：由于无法查询到当前差异备份对应的全备份版本，导致无法还原此差异备份，还原数据库异常终止；
解决方案：由于当前业务无过多要求，重新全备份，则后续的差异备份可以操作；如需深究则另行查询！

In this blog post I will try to explain about the error in detail. In the same context, long time back, I did write a blog post on: SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

Over there, I have explained details and co-relation of the various backup type i.e. Full, Differential and Transaction Log backups. I will refrain from rehashing them here again.

Recently, one of my friends asked about if we have differential backup, how we can find the full backup on which differential backup can be restored. If we go back to basics, the differential backup has all the changes in the database made since last full backup was taken.

Let us understand this concept using an example:
在这里插入图片描述
Once the script has been run we have below backups.

SQL SERVER - FIX: ERROR : Msg 3136, Level 16, State 1 - This differential backup cannot be restored because the database has not been restored to the correct earlier state restoreerror-01

Looking at the backup chain, it is clear that D3 is valid for F2. On the other hand D1 and D2 are valid and restorable on top of F1. Let us drop the database and try to restore D3 on top of F1.

在这里插入图片描述
Here is the output.

This means that first restore was successful and next one has error which means that this is not a valid differential backup to be restored. How would we figure out the correct sequence of restore? Well, there are multiple ways.

Have a look at SQL Server ErrorLog where we have successful backup messages. Here is what we saw in ERRORLOG while running above backups.

As highlighted above, we can find the full back up LSN from the message of differential backup.
Have a look at Standard Reports to find previous backup events.

SQL SERVER – SSMS: Backup and Restore Events Report

Run below query on the server from where backup was taken.

SQL SERVER – Get Database Backup History for a Single Database

Hope fully this blog demystifies and tells you usefulness of the messages in ERRORLOG and logging capability of SQL Server. Do let me know if you have ever encountered these errors in your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)