November 14, 2012 at 11:02 am
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
November 14, 2012 at 8:28 pm
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.
April 15, 2016 at 10:21 pm
You are using both DPM and native SQL at the same time to do backups. If you want DPM to do the backups (as you should) stop running SQL backups. You cannot run backups (unless it is a copy-only backup which doesn't truncate the log) from multiple sources. They will both be broken (except the full backups) because each log backup will mark the log LSN and truncate the log - as it should). This is why you are getting broken log chains.
May 24, 2016 at 4:14 am
While I haven't had a chance to use it myself, there is a tool that comes with DPM for recovering SQL databases. According to TechNet it does allow leaving the database in recovery mode and copying additional transaction log files needed for the point in time recovery.
Have a look at this TechNet article for more information https://technet.microsoft.com/en-us/library/jj674341(v=sc.12).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply