Log Shipping - Do we still do Full Backups

Question

Post reply

Log Shipping - Do we still do Full Backups

Adam Goss

SSC Journeyman

Points: 95
More actions
March 10, 2004 at 6:48 am

#161699

So far so good on setting up Log Shipping (LS).

I did run into some trouble performing an edition upgrade from MSSQL200 Standard to Enterprise. Not all of the LS tables and stored procedures were properly created. I got around this by manually copying them from the destination server which had Enterprise Edition installed from the start.

My question is this. Are we supposed to "trust" LS to work by stopping our full backups? If I have LS setup to backup tlogs every 15 minutes, copy them every 15 minutes, and apply them every 15 minutes when do I run a full backup? Prior to LS I ran fulls nightly and tlogs every two hours. Now If I run the full won't it truncate the tlog? How will that affect LS?

I'd appreciate any help on this issue.

Thanks,

Adam Goss
DBA

Viewing 8 posts - 1 through 7 (of 7 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Always back up your databases daily no matter what else you do. Say for example Monday morning you notice the log ship server had a problem with log shipping over the weekend and no trans logs have been applied, and due to the fact that they are older than your max retention period they have been deleted. Then your main production server dies. You've just lost the entire weekends data.

Shamless self promotion - read my blog http://sirsql.net

Adam Goss SSC Journeyman Points: 95 More actions · Answer 2

Thanks for the response.

What you describe is exactly why I haven't gone ahead with LS in my production environment. What I need to know is what happens to the destination database when I perform a full backup on the source database? Wont the destination DB get out of sync because of the loss of any transactions between the last LS tlog backup and the daily full backup?

Maybe I am missing some inherent magic in LS...

Adam Goss
DBA

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Nicholas Cain

SSC-Dedicated

Points: 33009

March 10, 2004 at 10:31 am

#498155

The backup itself will not truncate the log it will checkpoint it however so that SQL knows that it can recover from that point forward. The log backups will continue as normal.

There is inherent magic...as there are in a lot of these things....

Shamless self promotion - read my blog http://sirsql.net

Ken Klaft-80806 Say Hey Kid Points: 693 More actions · Answer 4

Call me Mr. Obvious here but I'll say it anyway: after you do your full backup on your production system, copy the backup file to your log shipping target server. If that's not possible, at least make sure it gets to a tape every night and there's a compatible tape drive in your log shipping target server.

Cheers,

Ken

Gary Andrews SSCommitted Points: 1639 More actions · Answer 5

Another obvious appraisal.

Say a single backup is performed on the production server and it becomes the starting point for your standby server. Then you ship logs for three weeks (maybe 60 or 70).

Say you have a failure on your standby server.

Now to get your standby databases up to speed requires your restoring from your only backup and then having 60 or 70 log restores to go through.

Again, obviously not a pretty picture.

GaryA