INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can I back up the principle database while mirroring is in effect without breaking the log chain?

Can I back up the principle database while mirroring is in effect without breaking the log chain?

Can I back up the principle database while mirroring is in effect without breaking the log chain?

(OP)
Hi all,
We're about to set up a mirror db in a remote DR site. At our local site, we very often have the need to take a backup of the principle db to plop in in the Test or Dev server for various testing purposes. Can I do this without breaking the log sequence that the mirror is receiving? Or do I have to go and restore the mirror at the moment I take a backup of the principle?
Thanks,
--Jim

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

You should be able to use the COPY_ONLY option...from BOL for BACKUP DATABASE:
[quote]
COPY_ONLY
Specifies that the backup not affect the normal sequence of backups. A copy-only does not affect the overall backup and restore procedures for the database.

You can create a copy-only backup for any type of backup. The effect of the COPY_ONLY option varies with the general backup type, as follows:

A data backup taken with the COPY_ONLY option cannot be used as a base backup for differential backups. Differential backups taken later will behave as if the copy-only backup does not exist.


A differential backup is unaffected by the COPY_ONLY option.


A log backup taken using the COPY_ONLY option does not truncate the transaction log.
[/copy]
-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

(OP)
Bill,
Great info, thanks very much!
--Jim

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

(OP)
I have one more related question--can the same Copy_Only be used in a Log-shipping scenario without upsetting that sequence?
--Jim

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

FYI we are doing log shipping where we do a full backup nightly without a problem. (no copy_only) I am not sure about transaction backups.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

(OP)
djj,
In your scenario, are you backing up the main db but leaving the remote (the log-shipping target) alone? That is, not doing a full-restore on the remote db and starting the sequence all over?

I ask because our situation the main backup is so huge, we really can only afford to ship a full backup to the site maybe once a year, so we pretty much have to keep the log-shipping thing going indefinitely, while at the same time we need local backups for purposes of testing, etc. So it's no problem to move a 200 GB db backukp from on our main site's backbone over to the test environment, but to ship that backup to the remote site, even on solid media via Fedex, on a regular basis is not feasible.

So I guess it boils down to this question: In a typical log-shipping scenerio, how often does one do a full backup/restore and re-start the log shipping? We have occasional server version upgrades, sometimes configuration changes and major schema upgrades that we need to do and I'm not sure which of those require a 'reboot' of the log-shipping sequence.
--Jim

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

Ours is a small shop. Thus the full backup every night.
The time to get a log shipping started would not compare to yours.

We redo the fail over server about once a year as it seems to do a good job of staying in sync.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

We are log shipping a 6TB database and perform weekly Full and daily Diff backups on the source using Netbackup. We just removed the log backups on the DB being shipped.

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.

RE: Can I back up the principle database while mirroring is in effect without breaking the log chain?

(OP)
thedude,
So in your case, the 6tb is being backed up on the source--but is that 6tb chunk being transfered to the shipping target and restored, and log shipping restarted, or are you just holding that backup locally and leaving the log-shipping rolling as normal?

I'm just trying to get a handle on what a 'typical' log-shipping scenario will look like, as this is our first foray into t. Our data center is tier 4 and we've had zero issues but our client SLA's are requiring (as they should) that we have a remote DR site with sufficient geographical separation.

So...If I start the log shipping to the DR site, would it be reasonably fair to say that I can let the shipping continue for, say, a full year with no 'reboot' of the process (assuming no failures on the source), all the while backing up the source daily with the copy_only option?
On that similar note, is there a 'recommended' interval in which to restore the target DR db with a fresh copy of the source, or is this something that theoretically would never happen unless some event happened at the source.

Thanks very much,
--Jim

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close