Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DPM and SQL backup issues

Posted on 2010-11-16
Medium Priority
Last Modified: 2012-05-10

I am backing up my MS SQL 2000 server with Data Protection Server 2007 (DPM). I was getting a lot of errors on the DPM server because I was also doing backups on the actual SQL server to be transferred to tape at night.  I figured out that the errors were because I was backing up the transaction logs and it was creating a discontinuity in the transaction log chain. To fix this I have stopped doing the transaction log backup and I am only doing a full back up once a day right before the tape backup runs, again for offsite purposes. My trouble now is that the transaction log is getting huge and creating performance problems. Apparently DPM does not truncate the log file when it does it thing. What is the best thing for me to do to correct this problem while keeping DPM errors to a minimum?
Thank you,
Question by:lsctech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 34149170
Please see if this helps:


Best Practices when backup SQL with DPM 2010
Here are some basic best practices for managing data with a DPM 2010 deployment:

With DPM 2010 you need to configure at least one Express Full Backup per day. The Express Full Backup backs up the database and log files and then truncates the log files, while still replicating only the changed blocks from the production server.
When protecting more than one copy of a database (such as when you are protecting mirrored databases) you should configure one node for full backups and the rest as copy backups. Copy backups do not truncate log files.
If the SQL Server node hosting the copy of a highly available database on which the Full Backup is taken goes down temporarily, it is not necessary to perform any steps on the nodes on which copy backups are taken. If it becomes necessary to switch to another node because the failed node will no longer be available, you need to reconfigure DPM to take a full backup of the new target node.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34152885
>> I am backing up my MS SQL 2000 server with Data Protection Server 2007 (DPM).

Hope you are aware how DPM works with SQL Server.
An excerpt from the site

"DPM uses a combination of transaction log replication and block-level synchronization in conjunction with the SQL Server VSS Writer to help ensure your ability to recover SQL Server databases. After the initial baseline copy of data, two parallel processes enable continuous data protection with integrity:

1. Transaction logs are continuously synchronized to the DPM server, as often as every 15 minutes.
2. An “express full” uses the SQL Server VSS Writer to identify which blocks have changed in the entire production database, and sends just the updated blocks or fragments. This provides a complete and consistent image of the data files on the DPM server or appliance. DPM maintains up to 512 shadow copies of the full SQL Server database(s) by storing only the differences between any two images."

So, scheduling a Transactional log backup every 15 or 30 minutes would keep your Log Filesize under control
As a one time step, just shrink your log file using

use ur_db_name
DBCCSHRINKFILE (ur_db_name_log, 1000);

where 1000 represents 1000MB the size to which the log file needs to be shrinked.
Do note that transactional log file size might increase to some amount till it is replicated to DPM server.

Author Comment

ID: 34154977
Thank you dba2dba, but I am still on DPM 2007. I do plan to upgrade in the next few months, but for now I have to fix what I got.

If I run Transaction log backups every 15 to 30 minutes I get a constant stream of errors on my DPM server, and a large portion of my backups are no good. That doesn't happen to your server? From what I have read you can't do transaction log backups while using DPM. What am I missing?
LVL 57

Accepted Solution

Raja Jegan R earned 2000 total points
ID: 34155979
>> From what I have read you can't do transaction log backups while using DPM

Suggested transactional log backup forgetting DPM in place.
Since DPM is in place, you need to Shrink your log file to keep its size in control (as and when required):

use ur_db_name
DBCCSHRINKFILE (ur_db_name_log, 1000);

Author Closing Comment

ID: 34173437
Thank you

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question