DPM with native SQL Log backup

Question

DPM with native SQL Log backup

Biank

Ten Centuries

Points: 1332
More actions
October 21, 2014 at 2:53 pm

#297680

Hello,
I'm wondering if I can take native SQL log backup in addition to DPM backups. We're using DPM for both databases (daily) and logs (every 15min). So, if I take SQL log backup separately, will it break anything in DPM and we won't be able to perform point it time recovery?
Thanks much!

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

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

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

Use the "Copy only" feature to do a native backup the database so you don't break the Point-in-Time natue of DPM, whatever that happens to stand for today. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Biank (10/21/2014)
So, if I take SQL log backup separately...

Out of curiosity, why would you want to do that?

😎

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 3

Ah... and to be clear, I was thinking datafile backup being done separately. I agree with Eirikur in asking, why would you ever want to take log file backups separately?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Jeff Moden (10/21/2014)
Use the "Copy only" feature to do a native backup the database so you don't break the Point-in-Time natue of DPM, whatever that happens to stand for today. 😉

You can use the COPY_ONLY flag to obtain a full backup without resetting the differential base. This means that any subsequent differential backup you take doesn't refer to the full backup you took with COPY_ONLY. As far as I know, DPM doesn't take differential backups, but it takes only full and log backups.

Transaction log is a different story. If you take a log backup with COPY_ONLY, you won't truncate the transaction log and DPM will back up that portion of the log regularly. What you cannot achieve using COPY_ONLY is a separate copy of the whole log chain. I explain this in a post in my blog that you can find here: http://spaghettidba.com/2014/01/22/copy_only-backups-and-log-shipping/

The post considers log shipping as the "primary" log backup destination, but you can assume it was DPM instead.

Hope this helps

-- Gianluca Sartori

Perry Whittle SSC Guru Points: 233894 More actions · Answer 5

Biank (10/21/2014)
Hello,
I'm wondering if I can take native SQL log backup in addition to DPM backups. We're using DPM for both databases (daily) and logs (every 15min). So, if I take SQL log backup separately, will it break anything in DPM and we won't be able to perform point it time recovery?
Thanks much!

Why on earth are you using DPM when you have the option of native backups, there are plenty of scripts around to create detailed, scheduled native jobs and they're a damn site better than DPM.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Biank Ten Centuries Points: 1332 More actions · Answer 6

Well, DPM is our corporate level backup solution so I have no control, we have to use it. We're doing this because we need another process to parse transaction logs regularly for auditnig purpose. This is why we can't use COPY_ONLY backups. Yes, we've been using other methods but didn't work. So, I'm trying to use log backups instead of live transaction logs.

I know DPM has an option to whether transcation log can be truncated or not. Also, DPM can restore the database in "recovering..." state so we can apply any log backups as needed. However, I'm trying to find a way to avoid this additional step.

So, I was wondering,,, if DPM just make Full daily and Differential (log backup) every 15min without truncating the log and run SQL log backup every 15min to truncate the log, can we still use DPM backups to do point-in-time recovery or we should use DMP and SQL log backups.

Thanks again!

Biank Ten Centuries Points: 1332 More actions · Answer 7

Thanks all.

We decided to use DPM for FULL backup only and Log backup will be handled by native SQL backup.