log backups and log shipping backups

Question

log backups and log shipping backups

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
July 28, 2010 at 2:48 pm

#146480

I have a database with FULL recovery model, so I can have a Full backup every week, differential backup every night and log backup every hour. I created a log shipping to this database. Everything seems working ‘til the point where log backups and log shipping backups don’t work together.
It is possible to keep my maintenance plan (full, differential and log backups) and disaster recovery plan (log shipping) active for the same database?
Regards

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

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

Shinoj Ramanathan SSC Eights! Points: 894 More actions · Answer 1

But why do you need this. As far as your TLog backup is done by log shipping you should be good right ?

Why would you need a Maintenance Plan and a Log shipping plan to take a backup if the strategy is to take one TLog backup every hour.

You can disable the Job that does the TLog backup from the Maintenance Plan.

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 2

Yes I can do it.

I was thinking in the case of my Server where the LogShipping is been placed (the standby database) crashes. Then I wont have a way to bring that database live. Then, I will need to restore the primary database from the full, differential and the logbackups... Instead of only full and differential 'cause I will lose one day of data.

it makes sense ?

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

How big is the database in question?

Is there a reason you have gone for L-shipping over mirror?

Is space/time an issue that prevents full backups each day?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

I'm confused, do you have log shipping taking tlog backups and separate t-log backups?

That won't work as log shipping needs to have every single log backup sent to the secondary server. If you interrupt the chain of log backups with a log backup you've made, then you have put transactions in those backups that don't get sent to the secondary.

The backups that are used for log shipping can be used to recover a crashed server. If you are worried about both servers crashing, send log backups to a third server. If the secondary crashes, and the primary doesn't, then you still have the primary. Your log backups that haven't been sent to the secondary will accumulate there.

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 5

Yes I do understand that. I actually know that the sync is out of date because both log backups. and the Restore wont "pick up" any .trn file.

The only thing that Im not sure (I havent done a test) its if I can use the logshipping backups (that are already storaged in a 3rd server) to restore the primary server? Have you done that before by any chance ? (thinking about primary server database crash as well).

Regards,

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

There is no "log shipping backup"

Log shipping is a process that uses normal log backups and sends them to be restored to another server in an automated fashion. The log shipping wizard automates this for you. It doesn't do anything that you cannot do yourself. You can disable the jobs and restore the log backups yourself.

The only thing that matters for log backups is that they must be in a continuous chain.

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 7

Geez! you are really a pain... lol

I just was trying to define one backup from another.. Seems like you are a guy that goes with the book... anyway!, I guess I couldnt explain what I really wanted to try. Not big deal!

I do know how log shipping and its log backups works.

thank you tho

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 8

If you understand log shipping, then you should know that a log backup is a log backup. As long as they're in sync, in a chain, they can be used to restore with the full backup from the same database.

I'm not trying to be a pain, but we often find people here that confuse terms and it's hard to discuss what is going on with them. The semantics matter. There isn't a "Log shipping backup", but there is a "transaction log backup made by log shipping jobs" There's a difference there.

I'm just trying to be clear.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 9

MTY-1082557 (7/29/2010)
Geez! you are really a pain... lol
I just was trying to define one backup from another.. Seems like you are a guy that goes with the book... anyway!, I guess I couldnt explain what I really wanted to try. Not big deal!
I do know how log shipping and its log backups works.
thank you tho

If you ask a questions, we will try to answer it; however if your question(s) are not clear or you havent given us enough information AND when asked dont make your requirements clear, then the advise is as good as your description.

Steve gave good and clear advise but for what ever reason you chose to throw it back. From re-reading your posts it seems that you understanding of the mechanisms you are using is flawed. What you are trying to do wont work! You either Log Ship and dont take tlog backups OR you dont Log Ship and take tlog backups, to apply (if needed) manually (hence why i suggested using mirror rather than Log Ship).

Either way, watch your language or at the very least consider the way your 'humour' may be taken. Basically respect those who have more experience then you. We dont 'stick by the book' for nothing. There are reasons for suggesting what we do and most DBA's would accept that and even if they did not, they would be respectful.

Adam Zacks-------------------------------------------Be Nice, Or Leave

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 10

HI Adam,

Thank you for your comment. Yes I was sarcastic and I apologize for that, I think Steve was nice and clear with his answer after my last post.

I do respect people here, that is why I'm a member and I ask a question because I'm sure there are very smart and experienced people.

Like you said, EVERYONE has to watch their language. There are ways to tell people when we are wrong about some concept, description or terminology. It’s always better to say, “Do you mean xxx?” than “There is no such xxxx”. No because a DBA is more experienced than other you have to make them feel uncomfortable about the mistake they are making. I have been helped many times here and many of them have the right words to guide me. I think is both ways.

Next time, I will pay more attention what I write before I post since English is my second language.

See you all around.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 11

MTY-1082557 (7/30/2010)
HI Adam,
Thank you for your comment. Yes I was sarcastic and I apologize for that, I think Steve was nice and clear with his answer after my last post.
I do respect people here, that is why I'm a member and I ask a question because I'm sure there are very smart and experienced people.
Like you said, EVERYONE has to watch their language. There are ways to tell people when we are wrong about some concept, description or terminology. It’s always better to say, “Do you mean xxx?” than “There is no such xxxx”. No because a DBA is more experienced than other you have to make them feel uncomfortable about the mistake they are making. I have been helped many times here and many of them have the right words to guide me. I think is both ways.
Next time, I will pay more attention what I write before I post since English is my second language.
See you all around.

Thanks for your response. Its appreciated.

A.

Adam Zacks-------------------------------------------Be Nice, Or Leave