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

[jsteph]

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

 

[SQLBill]

You should be able to use the COPY_ONLY option...from BOL for BACKUP DATABASE:

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

 

[jsteph]

Bill,
Great info, thanks very much!
--Jim

 

[jsteph]

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

 

[djj55]

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!

 

[jsteph]

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

 

[djj55]

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!

 

[biglebowski]

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.

 

[jsteph]

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