Thursday, December 06, 2007 2:54 PMHi,
I'm making backups of the database by first making a full backup and then differential backups. The differentials are backed up to separate files.
Restore of the full backup works fine, but I can't restore a differential backup. In Management Studio Express, I first do a full backup restore with option NO RECOVERY and then try to restore a differential backup. But this failes with the message:
"This differential backup cannot be restored because the database has not been restored to the correct earlier state."
Is it possible to restore a differential backup that is backed up to a separate file?
Thursday, December 06, 2007 3:01 PMModerator
If possible post the restoration script ?
You need to restore Full backup using With norecovery and then the differential backup using With RecoveryPls refer http://msdn2.microsoft.com/en-us/library/ms186858.aspx#restoring_full_n_differential_db_backups for more info
Thursday, December 06, 2007 3:33 PM
The example shows restoring from a single file 'AdventureWorks.bak'. In my case it's restoring from separate files. I'm running the following scripts (generated with Management Studio Express):Code BlockRESTORE DATABASE db
FROM DISK = N'D:\temp\db_FullDBBackup_200712010200.BAK'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE db
FROM DISK = N'D:\temp\db_DiffDBBackup_200712020200.BAK'
WITH FILE = 1, NOUNLOAD, STATS = 10
This doesn't look good. Both have FILE = 1. The differential should be higher, right?
Friday, December 07, 2007 4:43 AM
Make sure nobody did a full backup after that last one. If they did the differential is tied to that file.
- Proposed As Answer by sriharsha kolluru Friday, December 14, 2012 3:41 AM
Wednesday, January 02, 2008 9:26 PM
Just run this command for differential backup restore.
fromdisk ='the diff backup file name'
withnorecovery (or with recovery which ever u want) and see whether it works or not.
Please let me know if u get any errrors
Thursday, January 03, 2008 6:36 PMModerator
The key is that a differential backup is based on a particular full backup.
In order to restore a differential backup, you MUST restore the last full backup which was taken before that differential backup. No other backup will work.
The files are not an issue. the FILE= parameter refers to logical files within one physical backup file. If you use a different file for each backup, then they are always FILE 1 within each backup file.
- Proposed As Answer by sriharsha kolluru Friday, December 14, 2012 3:40 AM
Thursday, December 15, 2011 9:53 AMThe full backup restored was not the one taken just before the differential backup. That is why LSN is not matching.
Thursday, December 15, 2011 10:08 AM
Hope you not restoring all Diff backups..You only need to restore the very last diff backup.. i.e
restore fullback up with no recovery
restore the last diff backup with recovery.. use following script to get last 7 day backup report
SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb.dbo.backupset.database_name='yourDBNAme' and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date desc
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
- Edited by v.vtMicrosoft Community Contributor Thursday, December 15, 2011 10:08 AM
Friday, August 03, 2012 2:31 PM
Hi, I just had this problem myself and found this thread.
However I just found out the cause of this and in my case I had the wrong FULL backup according to the DIFF backup. Meaning: I did a FULL backup between my old FULL backup and the DIFF backup. Adding the (old) FULL backup was no problem but the DIFF backup did not match my FULL backup.
So find the FULL backup that corresponds with your DIFF files and id should work. Also, tick the right boxes in the Options page. (Restore With Recovery and Restore With No recovery)
- Proposed As Answer by Henrik Jutterström Friday, August 03, 2012 2:31 PM