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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
If you ever consider purchasing any Daossoft Software Products, DON'T expect any meaningful support - This article should convince you why!
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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