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


Restoring database from DPM snapshot


Restoring database from DPM snapshot

Author
Message
christina.constantinou
christina.constantinou
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 122
Hi there,

We are currently reviewing our backup routines/recovery plans as we found that we were making a number of duplicate back ups of a particular database. DPM takes a snapshot twice during the day in accordance with our SLA, but we are concerned that in the event of corruption or failure, we won't be able to restore the database to a different environment using the .mdf and .ldf files (as they have not been detached).

Could anyone tell me whether this is the case?

Many thanks in advance!

Christina
Vegard Hagen
Vegard Hagen
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 387
DPM = Data Protection Manager for anyone who didn't know that part.
I have no experience with this myself, but my understanding is that this product works by pausing all IO on the files while the snapshot is created, hence it is roughly the equivalent of the files actually being detached at snapshot creation time and your SQL Server will be able to bring the database back online if you restore these files.



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)


christina.constantinou
christina.constantinou
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 122
Thank you very much for that. I'll be back with the results of our testing if there are any unusual effects!

Thanks
Christina
jasona.work
jasona.work
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9663 Visits: 12515
Couple other things to watch out for with DPM and SQL that I've run into.

First, DPM can and will break your log chain, if you're doing backups using SQL and your DB is in Full recovery. See my discovery of this here: http://www.sqlservercentral.com/Forums/Topic1384776-1550-1.aspx
and here: http://www.sqlservercentral.com/Forums/Topic1387602-1550-1.aspx

Second, if you choose to recover to a network folder (where it will simply copy the MDF / LDF files,) it can only do this using recovery points "associated with an express full backup."

Jason
christina.constantinou
christina.constantinou
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 122
jasona.work (1/7/2013)
Couple other things to watch out for with DPM and SQL that I've run into.

First, DPM can and will break your log chain, if you're doing backups using SQL and your DB is in Full recovery. See my discovery of this here: http://www.sqlservercentral.com/Forums/Topic1384776-1550-1.aspx
and here: http://www.sqlservercentral.com/Forums/Topic1387602-1550-1.aspx

Second, if you choose to recover to a network folder (where it will simply copy the MDF / LDF files,) it can only do this using recovery points "associated with an express full backup."

Jason


Ok cool, will do. We're currently running the database in Simple recovery mode for a number of reasons, but it's good for a heads-up as we have many and various recovery models for different databases.

With regards to your second point - do you mean the DPM recovery points?

Thanks
jasona.work
jasona.work
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9663 Visits: 12515
christina.constantinou (1/7/2013)Ok cool, will do. We're currently running the database in Simple recovery mode for a number of reasons, but it's good for a heads-up as we have many and various recovery models for different databases.

With regards to your second point - do you mean the DPM recovery points?

Thanks


Simple actually will make your life easier, then, I'm using DPM ONLY for DBs in Simple Recovery, and haven't had issues with my Full Recovery DBs since (they get backed up via SQL jobs to disk, then DPM grabs those files.)

DPM gives you the ability to create "recovery points," which are times that you could recover your data to. Using a plain file backup as an example, I could set up DPM to create hourly recovery points (RP) of that file. So if someone deleted it at 11:15am, I could recover from the 11:00am RP and go on about my day. With items such as SQL DBs, DPM has two backup mechanisms: Recovery Points and Express Full Backups (EFB)
As best I can see it, think of the EFB as a full database backup, and a RP as a Differential backup. BUT because of the way DPM stores these backups, if you want to restore only the DB files (the MDF / LDF) you can NOT use the Differential backups, only the EFB. If you restore to an SQL Instance (the DB will be attached, and you can choose to have it operational and available, or left in a Recovering state,) you can choose one of your RPs to recover.

Basically, you should check with whoever is in charge of the DPM (if it's you, look over the settings for your DB backups) and make sure it's set to create EFBs at your chosen recovery times (your "snapshots.") Then you should be able to recover to your SLAs as expected.

Of course, don't take my word for all of this, TEST, TEST, TEST, and research elsewhere as well (the Technet forums on DPM are excellent!)
Jason
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