Log Shipping backups

Question

Log Shipping backups

Animal Magic

SSChampion

Points: 13964
More actions
April 1, 2007 at 9:06 am

#177546

I have a question regarding backups in a log shipping situation. I have 60+ databases on serverA which are shipping logs on the hour t serverB. The log backups (within the log shipping) are the only backup that take place, as i know i cant do seperate log backups as it screws the log shipping config.
My question is, should serverA die, i can bring serverB online ok, but to get ServerA back up would i log ship from B to A? or should i be taking daily full backups of the databases on serverB and then use that plus the logs to get serverA backup to speed?

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

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

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 1

Well, you have a couple of options.

1. When you want serverA back up, detach the databases from serverB and attach them to serverA. Problem with this one is that usually you have to copy the data files from one machine to another and while that's taking place, you can't bring serverA up.

2. When serverA is available, backup the databases in ServerB (make sure they're in full recovery mode). Then restore the databases to ServerA (leaving the databases in norecovery mode) At the last minute, backup the database logs and restore them with the recovery option.

3.

Regards,

Carlos

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 2

Hi,

may be a dump question, but hopefully you do full backups of serverA regularly?

regards

karl

Best regards
karl

Animal Magic SSChampion Points: 13964 More actions · Answer 3

Hi Karl,

Thanks for your response, this was part of my original uncertainties.

I currently dont do any backups on either ServerA or ServerB, just the log shipping backups. If a serverA database goes down i can get it back from serverB but doing a backup and restore, no problems there.

My problems arise if someone truncates a table, or drops a table etc, and before i have noticed the log has been applied on serverB, leaving me with a screwed database on both sides. I believe i should be doing a full backup on one of the servers (i was going to do one on serverB) incase this happens.

I have read conflicting articles/posts on whether or not doing a full backup on a log shipping database screws the log shipping process or not. some people say you cant as it truncates the log (i dont think it does truncate the log) and others say that you can do a backup and the serverB would ignore that backup command when it comes to restore the log as part of the log shipping process.

I know i should be doing a backup somewhere, but didn't want to screw up my log shipping by doing it in the wrong place or incorrectly.

I would be grateful if you could put my mind at rest Karl/anyone else and let me know what common practice would normally be.

Thanks in advance

john

Adrian Hains Ten Centuries Points: 1322 More actions · Answer 4

You may want to consult Books Online:

http://msdn2.microsoft.com/en-us/library/ms189085.aspx

It states that a full backup truncates the log if you are using simple recovery, and that it does not truncate the log if you are using full or bulk logged recovery.

HTH

Adrian Hains Ten Centuries Points: 1322 More actions · Answer 5

And to your original question - I run a similar config although with poor man's log shipping, I run some code to do log restores to my warm Express SKU server every N minutes. If I do have to failover to the warm server I intend to use reverse log shipping to bring the primary machine back online. I do redundant full backups to multiple machines as well, which I highly recommend. It's always nice to have a recent full backup to go to for when something doesn't go as planned (ask me how I know )

I'll have a brief annoying transition time to go back to the primary server but at least I'll get to schedule it for the lowest impact time.

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 6

You can do full database backups without messing up log shipping. I backup all databases in full each day and do log shipping almost continuosly to my failover server.

Regards,

Carlos

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 7

part of my logshipping procedures is a full backup with a restore on the standby server - one of my main reasons for logshipping is the implicit verification of my backups - if they restore to the standby they will also restore to a completely new system...

karl

Best regards
karl

Animal Magic SSChampion Points: 13964 More actions · Answer 8

Thanks to all for your replies. I will now implement a full backup on server A.

Adrian - thanks for the link, that explains why there is a misunderstanding on some posts as to whether or not the log is truncated or not during a full backup, as all my DBs are in full recovery model for the log shipping element im ok.