DPM and SQL backup issues

Posted on 2010-11-16
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
  • 2
  • 2

Expert Comment

Comment Utility
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
Comment Utility
>> 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

Comment Utility
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 500 total points
Comment Utility
>> 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

Comment Utility
Thank you

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

From Coral's  "So You Want To Play With Computers" Series Preface: What follows is a tweaked reprint from 2005/06. This is a True Story. The names have been changed to protect the guilty. While this deals with a fairly simple, text file recovery…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now