Restoring separate differential backup files

Question

Hi,

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?

Answer 1

If possible post the restoration script ?

You need to restore Full backup using With norecovery and then the differential backup using With Recovery

Pls refer http://msdn2.microsoft.com/en-us/library/ms186858.aspx#restoring_full_n_differential_db_backups for more info

- Deepak

Answer 2

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 Block

RESTORE DATABASE db
FROM  DISK = N'D:\temp\db_FullDBBackup_200712010200.BAK'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

 

 

Code Block

RESTORE DATABASE db
FROM  DISK = N'D:\temp\db_DiffDBBackup_200712020200.BAK'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

 

 

This doesn't look good. Both have FILE = 1. The differential should be higher, right?

 

 

Answer 3

Make sure nobody did a full backup after that last one.  If they did the differential is tied to that file.

Answer 4

Just run this command for differential backup restore.

 

restore database DBName

from disk ='the diff backup file name'

with norecovery (or with recovery which ever u want) and see whether it works or not.

 

Please let me know if u get any errrors

 

Narayan

 

 

Answer 5

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.

 

Answer 6

The full backup restored was not the one taken just before the differential backup. That is why LSN is not matching.

Answer 7

Hi

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

 

 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 8

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)

/Henrik