Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with Data Protection Manager and backing up SQL databases... Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 11:02 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 737, Visits: 5,460
I've resolved the problem I had with this, but I figured I'd see if anyone else had run into it and if so, how they resolved the problem.

I had setup DPM a while back, and configured it to backup the SQL DBs we use here. All seemed well, and I could use DPM to recover a DB to a specified DPM recovery point. To cover the possibility of needing more fine-grained recovery options, I also had the various SQL Servers running daily full and hourly transaction log backups, which were also backed up by the DPM to move them off the SQL server disks.

All seemed to be going well.

Until...
(You knew there would be an until, didn't you?)

We actually needed to do a point-in-time recovery of a DB. I put the needed BAK and TRN files on the server, and started setting up the restore in SSMS (I'm not good enough yet to just key up the T-SQL) The problem being, SSMS wouldn't load ANY of the TRNs, reporting a break in the log chain. So I copied the files to my local PC to keep trying, and found that somewhere between the 9pm DB backup and 10pm log backup, things went pear-shaped. At this point, the manager who had requested the restore proceeded to work around the problem (apparently he closed out a report that was only valid for the time it ran, or some such, without printing it, and the application he uses apparently won't let him specify a time. Pain-in-the-tuckus payroll programs...) while I continued to try to figure out what had happened.

As best I could tell, DPM also fired off a backup at about the time the full DB backup was running, which threw things into a cocked hat. My solution was to stop DPM from directly backing up the DBs and instead only backup the files created by the SQL backups. Thus far, this seems to have resolved the problem (I've done a couple test restores on my local PC, to various times)

Now, DPM, when you restore a DB, will NOT leave the DB in a "Restoring..." state, so you can't recover further using TLog backups. So it seems that if you want to use DPM for backing up SQL DBs, you either need to configure it to backup as often as you might need recovery points, or only use it for DBs in Simple recovery mode. Or setup similar to what I've done.


Now, before anyone jumps on me for not testing the backups, I did try spot restores, both from DPM and from the backup files, but I did not try doing point-in-time restores. Lesson learned, without too much damage (a manager who had to do some extra work, and a dent in my reputation for infallibility)

Jason
Post #1384776
Posted Wednesday, November 14, 2012 8:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
Yeah, dpm doesn't play nice with log backups. I ended up doing the same thing you did. Tsql backups to disk with dpm protecting the backups.
Post #1384958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse