Differential backup Restore

Question

Differential backup Restore

Jeyanthan

Valued Member

Points: 65
More actions
April 6, 2006 at 11:56 pm

#112798

Hi,
I could not sort this problem yet,
I have taken a full backup from Server A to Server B @5am,the differential backup @ 11am,
I restored the full backup to Server B with no recovery @ 11.30am
and continued to restore the differential backup ,while i got the error msg.
Server: Msg 3136, Level 16, State 1, Line 2
Cannot apply the backup on device 'D:\BACKUP\IMCL_NW\IMCL_DIF.BAK' to database 'IMCL'.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
-----------------------------------------------
need help from gys ...
rgds
Jey

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

devaraya Default port Points: 1455 More actions · Answer 1

Hi Jey,

First restore the Full backup with option Leave database read_only and restore additional trasaction logs (check this radio button in the restore database options tab)and then restore the differential backup.Pls check the names of mdf and ldf files when restoring to the exact database files.

Ex: pubs070406.mdf,pubs070406_log.ldf

Good Luck

Ramaa

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Rama,

You can't restore Transaction Logs and then restore a Differential.

Jey,

Are you sure you used the WITH NO_RECOVERY option on the Full restore?

Are you sure you have the correct Differential backup?

-SQLBill

Jeyanthan Valued Member Points: 65 More actions · Answer 3

Hi Bill,

I am 100% sure about the backup and norecovery,

but same time

i tried ,just taking the full backup & diff.backup in small time difference and try to restore,this case it was restoring..i cud not figur out the problem..

Jey

devaraya Default port Points: 1455 More actions · Answer 4

Hi Bill,

I am taking full backup on saturdays and daily incremental backups. I am restoring the db's with full backup and then differential backup through EM as i said above . It's working fine me. I am not getting any error. Is the procedure is correct?

Ramaa

devaraya Default port Points: 1455 More actions · Answer 5

Hi Jey,

Are you resolved ur problem or still hanging over there?

Ramaa

Balmukund SSCertifiable Points: 6648 More actions · Answer 6

Please note that the sequence of the full and the differential had to be followed.

So basically there should be a full database backup taken LATER than the one we were originally trying to restore from the IMCL_DIF.BAK file. So you need to restore the more recent full database backup followed by the latest differential backup successfully.

Easlier way to get proper sequence will be to have a look into SQL Error Logs and find proper sequqnce.

--This is quite simple to repro.

BACKUP DATABASE NewDB TO DISK='C:\NewDB_full1.bak' WITH INIT

BACKUP DATABASE NewDB TO DISK='C:\NewDB_full2.bak' WITH INIT

BACKUP DATABASE NewDB TO DISK='C:\NewDB_DIFF.bak' WITH DIFFERENTIAL

-- Now lets start restoring it

RESTORE DATABASE NewDB FROM DISK ='C:\NewDB_full1.bak' WITH NORECOVERY

RESTORE DATABASE NewDB FROM DISK = 'C:\NewDB_DIFF.bak' WITH RECOVERY

==================

Server: Msg 3136, Level 16, State 1, Line 1

Cannot apply the backup on device 'C:\NewDB_DIFF.bak' to database 'NewDB'.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Feel free to Ping me at bmlakhani@yahoo.com

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Rama,

This is what you said: restore additional trasaction logs (check this radio button in the restore database options tab)and then restore the differential backup.

That says you restore transaction logs and then the differential. You can't do that. It must be differential and then transaction logs.

-SQLBill

Jeyanthan Valued Member Points: 65 More actions · Answer 8

Hi Rama,

I aggry with Bill,its uppose to be in the order of

Full Backup,Differential bak,then Tlog Bak.

But is there any time difference for Full & diff,coz i took full on One day and Diff on other day.

Rgds

jey

SQLBill SSC Guru Points: 51440 More actions · Answer 9

Jey,

You can restore any differential that followed a full backup. Example:

Sunday - full backup

Monday - differential

Tuesday - differential

Wednesday - full backup

Thursday - differential

Friday - differential

Saturday - differential

You can restore the Sunday fullbackup and either Monday or Tuesdays differential. It all depends on when you want to recover to. You CANNOT restore the Sunday full backup and any of the Thursday, Friday, or Saturday differentials as they don't 'belong' to the Sunday Full backup. Likewise, you can restore the Wednesday full backup and either the Thursday, Friday or Saturday differential. (but only ONE differential gets restored per full restore). Each differential 'holds' ALL the changes since the last Full backup. So, Monday's diff has all the changes from the time of the Sunday full backup until the Monday diff was made. Tuesday's diff has all the changes since the Full backup, including those that happened on Monday.

Clear?

-SQLBill

skeane SSC Eights! Points: 877 More actions · Answer 10

Jey,

When you took your backups did you use the WITH INIT option for both? If not, you may have appended your backup onto an existing backup. When you restored, you would have restored the earlier database that is not a match for your differential, or vice versa. Try RESTORE HEADERONLY to inspect the backups in the set, then RESTORE DATABASE...WITH FILE=n if you need to grab the later backup.