Restoring a differential backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring a differential backup

Author

Topic

eddiefdz
Starting Member

21 Posts

Posted - 2010-03-15 : 10:14:16

Hello,

When restoring a differential backup, do I also need to restore any transaction log backups that I have done after the full backup??

For example, i did a full backup at 3:00am, then every hour after that, i do a transaction log backup, then at 12:00pm I do a differential backup.

If i needed to restore up the point of the differential backup, do I just restore the full backup and then apply the differential or do I have to restore the full, apply the transaction log backups, and the the differential?

I figure that all i would need to do is restore the Full and then apply the Diff, but I want to make sure just in case.

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-03-15 : 10:46:03

" I figure that all i would need to do is restore the Full and then apply the Diff,"

That's correct

(1) Restore a FULL backup

(2) Then restore a DIFFERENTIAL backup - taken After the Full backup (at point 1) but BEFORE any subsequent Full Backup

(3a) Optionally restore one, or more, TLog backups - in chronological order - starting from the first after the Differential backup

(3b) Or you can restore just the Full backup and then all TLog backups after that (i.e. without using the Differential backup)

If you attempt to restore a TLog backup out-of-sequence SQL will tell you - so its not critical / you don't have to start again!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-15 : 10:54:27

Just note that you have to restore the FULL backup WITH NORECOVERY. If you're applying transaction logs as well, the DIFFERENTIAL and all but the last log backup must also be restored WITH NORECOVERY

--
Gail Shaw
SQL Server MVP

eddiefdz
Starting Member

21 Posts

Posted - 2010-03-15 : 14:25:23

Ok, but my concern here is that if I am doing transaction log backups in between the FULL and the DIFF, it would not hurt me when i go to restore the FULL and the DIFF right? In other words i just want to confirm that the FULL and the DIFF is all I need and that there will be no data lost because of the Transaction Log backups that i ran between the FULL and the DIFF. Is this right?

Eddie Fernandez
IT Director
MTech

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-15 : 16:11:02

SQL won't let you miss transactions when restoring with a log chain. That would result in an inconsistent database.

Restore the full, restore the last diff. The last diff contains all the changes made since the full backup. Then all the log backups starting with the one straight after the diff. Those contain records of the changes done within that log backups' period.

--
Gail Shaw
SQL Server MVP

eddiefdz
Starting Member

21 Posts

Posted - 2010-03-15 : 17:08:15

Gail,

Thanks for your reply.

I understand what you are saying about restoring the FULL then the DIFF. But my question once again is, the DIFF will contain all the data from the last FULL regardless of any transaction log backups running between the FULL and the DIFF?? If i have a full backup that I run at 3:00am, then at 4, 5, 6, 7, 8am I run a transaction log backup, then at 9:00am i run a DIFF, all i would need if somthing went wrong would be the 3:00am FULL and the 9:00AM DIFF correct???? point being is I would not need the tranaction log backups in between... Am i right??

Eddie Fernandez
IT Director
MTech

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-15 : 17:11:07

Yes the DIFF will contain all data since the last FULL regardless of any tlog backups. That's the purpose of a DIFF.

You can't restore a full, then tlogs, and then a diff. It's full, diff, then tlog. Or full and tlog.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-15 : 17:38:53

As I said, the DIFF contains all of the changes since the last full backup

--
Gail Shaw
SQL Server MVP

eddiefdz
Starting Member

21 Posts

Posted - 2010-03-15 : 22:12:46

Thanks ALL for you replies.

Eddie Fernandez
IT Director
MTech

Kristen
Test

22859 Posts

Posted - 2010-03-16 : 04:35:47

"all but the last log backup must also be restored WITH NORECOVERY"

I restore them all WITH NORECOVERY - gives me time to think about the last one! - and then do


RESTORE DATABASE MyDatabaseName WITH RECOVERY

i.e. without specifying any File to restore from

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-16 : 06:01:00

I prefer that way as well.

The point is that all but last must have norecovery. The last one can, but it's not an absolute requirement

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources