Full, Diff, and Log Backups

Question

Full, Diff, and Log Backups

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
October 11, 2010 at 9:22 am

#239657

Hey Everyone
I am planning the restore of a database to another box. Nothing new about that.
The Full backup is scheduled for once weekly on friday
The Diff backup is everyday, and the Log backup is every 15 mins.
Now about the restore. I am trying to plan how to restore each backup in order, so that I do not break the log chain.
I know how to restore each, but my question is, how can I restore each without any issues when it comes to the logs and breaking the log chain?
Restore the Full on Friday at 2:00 pm, restore the Diff everyday, except friday at 2:00 pm, and then restore the logs one minute after they are taken from the primary database.
I am looking for advice on how I can best accomplish this. I need to keep track of each restore, so that I can see where the process is on the secondary.
Thanks
Andrew SQLDBA

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I must be missing something. Why would restoring anything break the log chain?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

I must have totally mis-understood what I have read in the SQL BOL.

So I can restore those other databases in a continuous loop, right after they are taken, and everything will be fine? I am in the process of testing this now.

If you could offer an explanation of that, I would greatly appreciate it

Thanks

Andrew SQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

AndrewSQLDBA (10/11/2010)
So I can restore those other databases in a continuous loop, right after they are taken, and everything will be fine?

Huh?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

What I meant was, can I restore each type of database backup without messing up the log chain?

Restore the Full every friday afternoon, and continue with the logs, restore the Diffs every afternoon, except friday, and continue with the log restore.

Andrew SQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No restores will break the log chain on the source database, and that's the only log chain that matters.

What are you trying to achieve here? A read-only reporting database? A test of all your backups? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55549 More actions · Answer 6

Basic steps for a "normal" restore:

1) Restore your most recent FULL

2) Restore your most recent DIFF (assuming it's more recent than the full)

3) Restore all the T-Logs more recent than the DIFF (or more recent than the FULL if there is no DIFF)

Note: Use NORECOVERY option on all the restores to enable each subsequent restore. Use RECOVERY

on the final t-log restore.

I'm also unclear on your goal. If it's something other than a straighforward restore to a point in time, then the above steps may not apply.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

This is to keep a secondary DR box up with the Primary box.

I want to restore the Full from the primary box to the secondary box once a week

Restore the Next Diff from the primary box to the secondary box, every afternoon

Restore the trans logs from the primary box to the secondary box, every 15 mins.

This will be happening everyday. I will need to restore the trans logs every day up until the Diff is restored. Then my logs will continue. Then on friday, the Full will be taken from the primary and restored on the secondary. So right before that can happen, I need to restore one last log and use WITH NO ROCOVERY. So that I can restore that Full

Does that make sense? It is a continuous, recurring restore of each on a daily cycle, all but the Full restore, and that will happen every friday afternoon.

Andrew SQLDBA

george sibbald SSC Guru Points: 104210 More actions · Answer 8

once you have restored the first full backup, you only need to restore the log backups, its called logshipping 🙂

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

AndrewSQLDBA (10/11/2010)
I want to restore the Full from the primary box to the secondary box once a week

You only need to do this once. Restore with standby if you want it read only, restore with norecovery if it's ok for it to be unavailable

Restore the Next Diff from the primary box to the secondary box, every afternoon

You don't need to do this at all.

Restore the trans logs from the primary box to the secondary box, every 15 mins.

Restore with standby if you want it read only, restore with norecovery if it's ok for it to be unavailable

Any particular reason you've built your own implementation of log shipping?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 10

Yes, because the secondary box is a DR box that is on a separate network that has only one way communications. I can only see the secondary box from the primary box, I cannot see the primary from the secondary. It would result in a IP conflict. Not my design, just something that I am having to work with.

Or I would surely use the Log Shipping that comes with SQL Server.

Thanks

Andrew SQLDBA

WayneS SSC Guru Points: 95461 More actions · Answer 11

AndrewSQLDBA (10/11/2010)
Yes, because the secondary box is a DR box that is on a separate network that has only one way communications. I can only see the secondary box from the primary box, I cannot see the primary from the secondary. It would result in a IP conflict. Not my design, just something that I am having to work with.
Or I would surely use the Log Shipping that comes with SQL Server.
Thanks
Andrew SQLDBA

When setting up log shipping, the primary backup does NOT have to be on a local server, though it usually is. Since you do have one-way communication from primary to secondary, you can establish the log shipping backups to go directly to the secondary server.

A bigger issue, IMO, is that you can't connect the secondary to the network. How in the world do you expect your disaster recovery plan to work, if it isn't available? IMO, this is a very poorly implemented DR plan.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

george sibbald SSC Guru Points: 104210 More actions · Answer 12

You still only need to send over and restore the log backups.

---------------------------------------------------------------------

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 13

Wayne

I never once said the secondary box was not on a network, of course it is. I stated that was not on the same network. Meaning it is on a different subnet. Like I said, I did not plan the network design, but my SQL boxes are on it, so I have to use it.

Andrew SQLDBA

paul.knibbs SSCoach Points: 15320 More actions · Answer 14

Wayne's question still applies, though--if the primary box fails, how are you going to fail over to this secondary server if it isn't on the same subnet?