转自：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.
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)