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

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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 40
SQL Server Resume 5 46
Find special characters using tSQL 6 20
SQL - Ordering Supervisor Hierarchy 2 15
If you ever consider purchasing any Daossoft Software Products, DON'T expect any meaningful support - This article should convince you why!
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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