Apply restore to a database with full and differential at different time.

Question

Apply restore to a database with full and differential at different time.

sqlfriend

SSC Guru

Points: 52781
More actions
November 18, 2022 at 6:23 pm

#4114984

I am trying to test to restore database from prod server to test server. It is a small database. On production server we have full backup at weekend, and then differential at weekday nights,
I am using UNC drive to connect to the backup location on production. I can add files of a full backup and a differential backup. It restored successfully. But if I do separately that is to do the full backup restore first with noRecovery, it is OK, then I do a separate restore with a differential backup, it will error out saying no full backup is selected.
Is there a way I can apply differential separately instead of having to do full and differential together?
Thanks

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

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

No. A differential backup is just what the name implies. It is used to restored changes to the full backup. You can't restore the diff w/o first restoring the full.

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thank you,

What I meant is can I restore the full backup and then differential backup at different time?

For example,

I restore full backup from production to test server database on Sunday night. with noRecovery mode.

Then at Monday night, can I just restore from differential backup to apply to the noRecovery mode test database?

Or I have to restore full again along with differential backup.

The test server database is just a copy for disaster recovery

Thanks,

ratbak Ten Centuries Points: 1382 More actions · Answer 3

Only if you restore the full backup with no recovery (i.e., the database to which you restored is in restoring mode).

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Yes, that is what I did.

So first I restore the full backup with no recovery.

Then after an hour later I restore the database with diffrential only, then it errors out saying full backup is not selected.

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

I found this can be done from T_SQL, but error out by using SSMS, it seems a bug from SSMS, or it has to select full backup in the list from SSMS along with differential or transaction log backups. It cannot do separately

durai nagarajan SSCoach Points: 15778 More actions · Answer 6

Post full backup restored as "No restore" option , it will accept only the next LSN differential backup, hope you are doing the same.

If in between some one restores a log or diff back, hope you are taking latest diff back or next log backup for the same?.

Post the error also.

Regards
Durai Nagarajan

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thank you.

I found out if I use SSMS to do that, it failed with error: full backup not selected even I use the right LSN differential or transaction log backup.

But If I use T_SQL, it works fine. I am using SSMS 18.1.

This could be a defect of SSMS.

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

You can prepare the restore with the settings and options with SSMS, but instead of running it, "Script to query window" and compare your T-SQL script with the SSMS generated script if you are curious about the differences.

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

Yes, thanks that is what I did, in SSMS I have to choose the full backup and differential,

then I generated the script, and comment out the full backup line, and only run differential one, it works.