SQL恢复备份遇到错误 - his differential backup cannot be restored because the database has not been restored

最新推荐文章于 2021-02-28 09:36:12 发布

jameszhouhvs

最新推荐文章于 2021-02-28 09:36:12 发布

阅读量2.8k

点赞数

分类专栏： Tips and Resources SQL

本文链接：https://blog.youkuaiyun.com/jameszhou/article/details/16858747

版权

Tips and Resources 同时被 2 个专栏收录

209 篇文章

订阅专栏

SQL

4 篇文章

订阅专栏

恢复数据备份时遇到的错误：

"This differential backup cannot be restored because the database has not been restored to the correct earlier state"

可查询备份历史，验证备份文件序列是否正确。在数据库中查询备份历史，可使用来自http://jasonbrimhall.info/2012/11/28/differential-restore-fail/ 的如下脚本：

DECLARE @SQLVer SQL_VARIANT
		,@DBName VARCHAR(128)
		,@NumDays	SMALLINT
 
SET @DBName = 'YourDatabaseNameHere'
;
SET @NumDays = 14
;
SET @SQLVer = SERVERPROPERTY('ProductVersion');
 
SELECT a.database_name,a.backup_start_date
		,b.physical_device_name AS BackupPath
		,a.position
		,a.type
		,a.backup_size/1024/1024 AS BackupSizeMB
		,CASE 
			WHEN CONVERT(INT,LEFT(CONVERT(VARCHAR(12),@SQLVer),2)) < 10 
				THEN 0
				ELSE a.compressed_backup_size/1024/1024 
			END AS CompressedBackMB
	FROM msdb.dbo.backupset a
		INNER JOIN msdb.dbo.backupmediafamily b
			ON a.media_set_id = b.media_set_id
	WHERE a.type IN ('D','I')
		And a.backup_start_date > GETDATE()- @NumDays
		AND a.database_name = ISNULL(@DBName,a.database_name)
	ORDER BY a.database_name,a.backup_start_date;
 
GO

如果已经无法访问原始数据库，只有备份文件，可以使用如下SQL命令，列出备份文件的序列信息：

restore headeronly from disk='C:\Backup\full_backup_1.bak'

restore headeronly from disk='C:\Backup\tlog1.trn'

restore headeronly from disk='C:\Backup\differential_backup_1.bak'

“the First LSN of 1^st transaction log to be restored matches the CheckpointLSN in the full database backup. From there onwards, you can determine the serial order such that the LastLSN of T-Log backup 1 matches FirstLSN of T-log backup 2 and so on. This is because Transaction Log backups are sequential in nature.

For differential backups, you can notice that their DatabaseBackupLSN should be the same as the CheckpointLSN in the full database backup.

Also note that as differential backups are cumulative in nature, restoring the latest differential backup (identified by larger CheckpointLSN) will save some time in the Restore process.”

来自：http://blogs.msdn.com/b/sqlserverfaq/archive/2013/05/22/how-do-i-map-a-differential-transaction-log-backup-to-its-full-backup-without-using-msdb.aspx