SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with Data Protection Manager and backing up SQL databases...


Issue with Data Protection Manager and backing up SQL databases...

Author
Message
jasona.work
jasona.work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10323 Visits: 12598
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
SpringTownDBA
SpringTownDBA
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 1499
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.
frank.pivonka
frank.pivonka
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 67
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.
Don Tainsh
Don Tainsh
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 101
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search