Automated DPM SQL restore from one database to a reporting database???

Question

Goal:  Nightly DPM 2010 restore from clustered SQL Production database to overwrite Production Reporting Database on same server.

I know this cannot be done via DPM 2010 GUI and needs to be approached via DPM's Powershell Cmdlets.  I'm just getting stuck in the forest of cmdlet syntax.  I'm not worried about how to schedule that (I'll just use task scheduler), I just need to figure out how to finish this script.

Here is where I am at so far (I know I have a bit to go still).  Any help would be apprecaited:

$pg = Get-ProtectionGroup -DPMServername <SERVER NAME> #Set dpm server

$ds = Get-Datasource - ProtectionGroup $pg[x]  # Get SQL Data protection group (x=protection group position in array)

$rp = Get-RecoveryPoint -Datasource $ds[x]  # Get SQL Database to restore from (x=database position in array)

$rp[-1] # Returns lastest recovery point for SQL Database to be restored from

so now that I have the recovery point for the source Database, how do I go about overwriting a different destination database on the same server and instance?  I think i have to use the following Cmdlets, but I need to nail down the syntax and get this completed asap (reminder: clustered instance of sql):

Get-RecoverableItem

New-RecoveryOption

Recover-RecoverableItem

 

Thank you in advance for your help :)

Ian

Answer 1

Hi Ian.Davies,

 

Thanks for your post.

Since this forum is only for SQL Server and this question is more related to Data Protection Manager, I would like to recommend that you ask it in System Center Data Protection Manager forum and you will get specific support.

---

Regards,
Tom Li

Answer 2

Thank you,

 

I will do that.  Though for those that are interested my script has snificantly changed.  Here is the current version which will restore the files, I just now need it to restore on top of the reporting database directy:

 

# Set static variables
$DPMServerName = "<DMP Server>"
$SQLProtectionGroupName = "<SQL Protection Group>"
$Database = "<database to restore>"
$RestoreServerName = "<restore server name>"
$RestorePath = "<restore location>"

# Set restore to pull from dpm server with protection group
$SQLProtectionGroup=Get-ProtectionGroup $DPMServerName | where-object { $_.FriendlyName -eq $SQLProtectionGroupName}

#Set the datasource to pull from protection group and Database
$SQLDataSource = Get-DataSource -ProtectionGroup $SQLProtectionGroup | Where-object { $_.name -eq $database}

#pull all express full recoverypoints from database
$RecoveryPoints = Get-Recoverypoint $SQLDataSource | where-object { $_.HasFastRecoveryMarker -eq "Fast"}

#Configure the Recovery Settings to pull from a SQL database and copy the files to the desired restore location
$RecoveryOption = New-RecoveryOption -SQL -TargetServer $RestoreServerName -RecoveryLocation CopyToFolder -RecoveryType Restore -TargetLocation $RestorePath

#Trigger the system to restore the latest Full-Express recovery point.
$RestoreJob = Recover-RecoverableItem -RecoverableItem $RecoveryPoints[-1] -RecoveryOption $RecoveryOption

Answer 3

Have you had any luck on getting it to restore directly to the database?

Answer 4

sort of.

 

My script has significantly changed since this post.  I can get you my current script if your interested.  My current process is as follows:

 

1) have powershell connect to the sql server that needs the restore and call a job to drop the database that needs to be overwritten.

2) Check restore points for last nights full express backup

3) If backup doesn't exist check again in 5 min - stays in look until it finds the backup

4) Restore the database to a local drive on the database server that needs to be restored.

5)  Move the restored mdb, ldf, ndf files to location the database needs for mounting

6)  clean up the restored directory structure (delete unnecessary folders)

7) have powershell connect to sql server to call a sql job that mounts the database

 

Its a bit crazy but it works.

 

Ian

Answer 5

I would be interested in seeing your current script as well.

Answer 6

Here is the current script that I'm using.  It could use a little cleanup and more effecient coding, but for now it is working just fine for me:

 

#Add data protection manager powershell snapin
#Add-PSSnapin Microsoft.DataProtectionManager.Powershell
#Add powershell sql snapin -note this is run from dpm which already has sql powershell registered
get-pssnapin -registered | add-pssnapin -passthru

#Drop the current sql reporting database using the sql powershell provider
cd sqlserver:\sql\%servername%\default\jobserver\jobs
$DropDatabase = dir | where-object {$_.name -eq "Daily: reporting Delete"}
$DropDatabase.invoke()

#Move out of the SQL Server Provider
W:

# Set static variables
$DPMServerName = "%dpmserver%"
$SQLProtectionGroupName = "%protection group name%"
$Database = "%databasename%"
$RestoreServerName = "%restore server name%"
$RestorePath = "W:\Nightly Restore"
$RestoreSourceDate = ""

#wait for nightly full express backup to complete
while ($restoresourcedate.day -ne ((get-date).adddays(-1).day)) {
    write-host "Waiting for Express Full Backup to complete (will notify every 5 min)"
    start-sleep -s 500
    # Set restore to pull from dpm server with protection group of SQL Data
    $SQLProtectionGroup=Get-ProtectionGroup $DPMServerName | where-object { $_.FriendlyName -eq $SQLProtectionGroupName}

    #Set the datasource to pull from protection group SQL Data and Database
    $SQLDataSource = Get-DataSource -ProtectionGroup $SQLProtectionGroup | Where-object { $_.name -eq $database}

    #pull all express full recoverypoints from "%database%"
    $RecoveryPoints = Get-Recoverypoint $SQLDataSource | where-object { $_.HasFastRecoveryMarker -eq "Fast"}

    #Set Restorepoint to equal last known good Full Express Backup
    $RestorePoint = $recoverypoints[-1]

    #Set Restore Date
    $RestoreSourceDate = $RestorePoint.RepresentedPointInTime
}
#Configure the Recovery Settings to pull from a SQL database and copy the files to "W:\Nightly Restore" on "%restore server%"
$RecoveryOption = New-RecoveryOption -SQL -TargetServer $RestoreServerName -RecoveryLocation CopyToFolder -RecoveryType Restore -TargetLocation $RestorePath

#Trigger the system to restore the latest Full-Express recovery point.
$RestoreJob = Recover-RecoverableItem -RecoverableItem $Restorepoint -RecoveryOption $RecoveryOption

#wait for restore job to complete
while (! $RestoreJob.hasCompleted)
{
    # Show a progress bar
    Write-Host "." -NoNewLine
    Start-Sleep 5
}
Write-Host
if($recoveryJob.Status -ne "Succeeded")
{
Write-Host "Recovery Failed" -ForeGroundColor Red
}
else
{
Write-Host "Recovery Successful" -ForeGroundColor Green
}

#remap network drive
net use w: /delete
net use w:  \\%restore Server%\w$

#Move restored files to main directory for database mounting.  Please note that ni* = "Nightly Restore" directory,
w:
cd \
cd ni*
cd dpm*
cd %server name%
cd %database%
cd d-volume
cd ms*
cd ms*
cd data
move *.* w:\%mount directory%
start-sleep 60
cd \
cd ni*
cd dpm*
cd %server name%
cd %database%
cd e-volume
cd ms*
cd ms*
cd data
move *.ldf w:\%mount directory%
start-sleep 60

#Remove restore Directory Cleanup
set-location "W:\Nightly Restore"
get-childitem -recurse | remove-item -recurse

#Mount the Restored Database via the sql provider

cd sqlserver:\sql\%sql server%\default\jobserver\jobs
$MountDatabase = dir | where-object {$_.name -eq "Daily: Create Reporting"}
$MountDatabase.invoke()