• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3326
  • Last Modified:

DPM and SQL backup issues

Hello,

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,
0
lsctech
Asked:
lsctech
  • 2
  • 2
1 Solution
 
dba2dbaCommented:
Please see if this helps:

http://blogs.technet.com/b/bettertogether/archive/2010/08/13/why-dpm-2010-and-sql-are-better-together.aspx


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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
http://www.microsoft.com/systemcenter/en/us/data-protection-manager/dpm-protect-sql.aspx

"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
GO
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.
0
 
lsctechAuthor Commented:
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.

rrjagan17:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
GO
DBCCSHRINKFILE (ur_db_name_log, 1000);
0
 
lsctechAuthor Commented:
Thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now