none
DPM 2010 Powershell script to recover SQL database RRS feed

  • Question

  • Hello all,

    This is my 1st post on here so please bear with me but I'm looking for some assistance with scripting an automated SQL restore using DPM 2010.  I'm not a script expert and I'm struggling to get my head around what's been suggested in the "Restore SQL 2008 Recovery Point in DPM 2010 to second SQL server via script" thread.

    Essentially what I've got is a production SQL database being backed up using DPM 2010 at head office, the DPM backup of the SQL db's is then replicated to a secondary DPM server at a remote site.  I have a 2nd SQL server at the remote site that I want to automate the restore of the SQL DB's from head office to.  I can run Wilson Souza's script but it errors as my drive configuartions difer between to 2 SQL servers.

    SQL Server at Head Office
    DB Location F:\Data
    Logfile Location E:\Logs

    SQL Server at remote site
    DB Location E:\data
    Logfile Location E:\Logs

    Essentailly what I'm after is a script that I can automate that runs on the remote DPM server to restore the latest replicated head office live DB backup to the remote SQL server with the correct file locations.

    Hope that makes sense, thanks in advance :)

    Friday, May 31, 2013 1:51 PM

Answers

  • I'll answer the latest version as you triple posted :)

    This is a modified version of Wilsons (and rockyMtnRajah - who posted in the same thread as Wilson) ...he added the ability to restore to different DB & Log locations on the destination server)

    Original Wilsons Script: http://social.technet.microsoft.com/Forums/en-US/dpmsqlbackup/thread/e5e50339-5707-4e72-bb9a-56f6d60ba926

    Additions by rockyMtnRajah: http://blog.aggregatedintelligence.com/2012/01/dpmrestoring-protected-sqlserver.html

    This is tested and working on DPM 2012 SP1 though. I hardcoded my Static Variables as this was only ever designed to restore 1 database for an automated consistency check to be run on it - If you want to reuse it you can put the static vars in params() like Wilson did though.

    Also this one logs to the event log on the server it is run from. 

    ################################################################################
    # Name: RestoreSQLDB.ps1                                                       #
    # Version: 1.04                                                                #
    # Description: This Script Will Restore The Latest Express Full Backup Of      #
    # A Database Originating From A Standalone Or Clustered 'SQL Server A' To      #
    # A Standalone 'SQL Server B'.                                                 #
    # The Database Can Be Renamed On The Destination SQL Server.                   #
    # The File Paths Of The MDF & LDF Files Can Be Changed Too.                    #
    # The $PerformRestore Var Must Be Set To $true To Actually Perform The Restore #
    # This Script Requires DPM Remote Administration Tools (CLI) Are Installed.    #
    # Version Modifications:                                                       #
    #                                                                              #
    ################################################################################
    #To Sign This Script Run These Commands Separately From PS After Requesting A CodeSigning Certificate
    #$Certificate = Get-ChildItem cert:\CurrentUser\My -Codesign
    #Set-AuthenticodeSignature ".\RestoreSQLDB.ps1" -Certificate $Certificate
    #Example - Run From CMD Line
    #C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NonInteractive -NoLogo -File "<PATHTOFILE>\RestoreSQLDB.ps1"
    #Static Variables
        $DPMServer = "DPMSRV" #Name Of DPM Server Prtotecting SQL DB
        $ProtectionGroupSQLStr = "Protection Group SQL Server" #Name Of DPM Protection Group To Restore From
        $SourceServerName = "CLUSTER\TESTINST" #If Standalone SQL Then Should Be In The Format <SERVERNAME> Or <SERVERNAME>\<INSTANCENAME> If Named Instance
        $DestinationServerName = "DESTSRV" #Name Of Server That DB Will Be Restored To - If Named Instance Then Should Be In The Format <SERVERNAME>\<INSTANCENAME>
        $SQLDatabaseName = "Test_Backup" #Name Of DB To Restore
        $DestinationDatabaseName = "$SQLDatabaseName"+"_CC" #Name Of The Database That Will Be Created On $DestinationServerName
        $DestinationMDFPath = "E:\Data" #Check - Dependent On The Restore Server
        $DestinationLDFPath = "E:\Logs" #Check - Dependent On The Restore Server
        $PerformRestore = $true #$true To Actually Do Restore $false
        $LoggingLogName = "Windows PowerShell" #Event Log To Log Errors To
        $LoggingSource = "PowerShell" #Event Source To Log As
        $LoggingEventID = "65535" #Event Log ID To Use
    #It Begins!!!!
        Import-Module DataProtectionManager        
        Connect-DPMServer $DPMServer; #Conenct To DPM Server
    	
        #Get PG Object Named $ProtectionGroupSQLStr & Store As A Var
        $ProtectionGroupSQLObj = Get-ProtectionGroup $DPMServer | Where-Object { $_.FriendlyName -eq $ProtectionGroupSQLStr} 
        
        #Get DataSource With Name $SQLDatabaseName Running On Instance $SourceServerName & Store As A Var
        $DataSourceSQLObj = Get-DataSource -ProtectionGroup $ProtectionGroupSQLObj | Where-Object { $_.Name -eq $SQLDatabaseName -and $_.Instance -eq $SourceServerName} 
    	If ($DataSourceSQLObj -ne $null) #Only Continue If DB Exists
            { 
            #Add $DataSource As A SQLDataSource And Store As A Var
            $SQLDatabases = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$DataSourceSQLObj;
            #Get The Latest Disk Based (Express Full) Backup - Fails With Incremental Sync.
            $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.Location -eq "Disk"}  | Sort-Object BackupTime -Desc;
            
            If ($RecoveryPointsSQLObj.Count) #Check More Than 1 RP Is Returned
            { 
                $RecoveryPointToRestore = $RecoveryPointsSQLObj[0]; #Get The Latest RP (1st In List)
            } 
            Else #If Only 1 RP Is Returned
            { 
                $RecoveryPointToRestore = $RecoveryPointsSQLObj; 
            } 
            
            If ($RecoveryPointToRestore -eq $null) #If No RP's Are Returned...
            { 
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Failed. RP For DB: $SQLDatabaseName On: $SourceServerName Not Found In PG $ProtectionGroupSQLStr"
                Return 
            } 
                    
            $length = $RecoveryPointToRestore.PhysicalPath.Length; #Return Number Of Files (i.e. LDF And MDF Files) - 2 = 1x LDF and 1x MDF 
            
            #Setup The Alternative DB Object Ready For Restore - Create The Objects & Add As Many FileLocationMapping Placeholders As There Are Files To $AlternativeDatabaseObj
            $AlternativeDatabaseObj = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType; 
            $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length; 
            $AlternativeDatabaseObj.LocationMapping = $LocationMapping 
            
            $i = 0; #Resets The Count (See While Loop Below)
            While($i -lt $length) #Perform The While Loop While $i Is Less Than The Number Of Files To Restore ($length). Add The Crrent File Names And Locations For Each File To Be Restored 
            {        
                $AlternativeDatabaseObj.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping; #Create The Object
                $AlternativeDatabaseObj.LocationMapping[$i].FileName = $RecoveryPointToRestore.FileSpecifications[$i].FileSpecification; #Set File Name For Files
                $AlternativeDatabaseObj.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RecoveryPointToRestore.PhysicalPath[$i]); #Set Source Location (Path) For Files
                If ($AlternativeDatabaseObj.LocationMapping[$i].FileName.ToLower().EndsWith(".ldf")) #If LDF File Set Destination Location As $DestinationLDFPath
                { 
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationLDFPath 
                } 
                Else #If MDF File Set Destination Location As $DestinationMDFPath
                { 
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationMDFPath 
                }        
                $i++; #Increment Counter (Move Onto Next File)
            } 
            $AlternativeDatabaseObj.InstanceName = $DestinationServerName;  #Set Destination Server Name. If Restoring To Named Instance Include The Instance Name 
            $AlternativeDatabaseObj.DatabaseName = $DestinationDatabaseName; #Set Destination DB Name
            
            #Create A Recovery Option Variable Targetted To The Destination Server, Set To Rename The DB And Use The $AlternativeDatabaseObj Details Created Earlier
            $ROP = New-RecoveryOption -TargetServer $DestinationServerName -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $AlternativeDatabaseObj; 
                
            #Load SQL SMO Class - Required To Check If DB Exists On $DestinationServerName
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null 
            #Create A New Object (SMO) Pass It The $DestinationServerName Variable And Store As A Var
            $SQLServerManagement = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server ($DestinationServerName)
            #If DB Exists At Destination, Write To Event Log
            If ($SQLServerManagement.databases[$DestinationDatabaseName] -ne $null)
            {
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Error: DB $DestinationDatabaseName Already Exists On $DestinationServerName - Restore Will Fail"
            }
                        
            If ($PerformRestore) #Only Run Restore If $PerformRestore Is $true
            { 
                $RestoreJob = Recover-RecoverableItem -RecoverableItem $RecoveryPointToRestore -RecoveryOption $ROP; #Start The Restore Operation Using The $ROP Recovery Option Var
                #The While Loop Below Effectively Pauses The Script Until The Succeeded Or Failed If Clauses Are Encountered. 
                $Wait = 2; #Initial Wait Time
                While ($RestoreJob -ne $null -and $RestoreJob.HasCompleted -eq $false) 
                { 
                    Start-Sleep -Seconds $Wait; 
                    $Wait = 20; 
                } 
                
                If($RestoreJob.Status -ne "Succeeded") #If Job Fails Write Appropriately To Event Log
                { 
                    Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Status: $($RestoreJob.Status)`n Start: $($RestoreJob.StartTime)`n  End: $($RestoreJob.EndTime)"
                } 
                Else #If Job Completes Write To Event Log
                { 
                    Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Status: $($RestoreJob.Status)`n Start: $($RestoreJob.StartTime)`n  End: $($RestoreJob.EndTime)"
                } 
                
                $td = (New-Timespan -Start $RestoreJob.StartTime -end $RestoreJob.EndTime) #Calculate Time Taken To Restore & Write To Event Log
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Elapsed time: Hours: $($td.Hours) Minutes:$($td.Minutes) Seconds:$($td.Seconds) MSecs:$($td.Milliseconds)"
            } 
            Else #If $PerformRestore Is Set To $false Write To Event Log
            { 
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "PerformRestore Varible Is $false - Restore Will Not Happen"
            } 
        } 
        Else #If DB Doesnt Exist As DataSource In DPM Protection Group Write Error To Event Log
        { 
            Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Database $SQLDatabaseName On $SourceServerName Does Not Exist. Nothing To Restore!"
        } 
        
        Disconnect-DPMServer $DPMServer #Disconnect DPM Server

    Cheers

    EDIT: Looks like the PS formatter here doesnt like escape characters ` - removed them
    EDIT: Finally found name of the other source for this script!

    • Edited by IT Badger Monday, June 3, 2013 11:12 AM More corrections!
    • Proposed as answer by IT Badger Monday, June 3, 2013 12:34 PM
    • Marked as answer by mannd75 Thursday, August 8, 2013 3:07 PM
    Monday, June 3, 2013 7:08 AM
  • Right after testing on a 2010 server this works bar the $RecoveryPointsSQLObj variable.

    You should change the variable above to :

    $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.DataLocation -eq "Disk"}  | Sort-Object BackupTime -Desc;

    Note the location property (either disk or media) in 2010 is DataLocation and not Location

    Other than that it should work fine ...does for me anyway

    Cheers

    • Marked as answer by mannd75 Thursday, August 8, 2013 3:07 PM
    Monday, June 3, 2013 10:39 AM

All replies

  • I'll answer the latest version as you triple posted :)

    This is a modified version of Wilsons (and rockyMtnRajah - who posted in the same thread as Wilson) ...he added the ability to restore to different DB & Log locations on the destination server)

    Original Wilsons Script: http://social.technet.microsoft.com/Forums/en-US/dpmsqlbackup/thread/e5e50339-5707-4e72-bb9a-56f6d60ba926

    Additions by rockyMtnRajah: http://blog.aggregatedintelligence.com/2012/01/dpmrestoring-protected-sqlserver.html

    This is tested and working on DPM 2012 SP1 though. I hardcoded my Static Variables as this was only ever designed to restore 1 database for an automated consistency check to be run on it - If you want to reuse it you can put the static vars in params() like Wilson did though.

    Also this one logs to the event log on the server it is run from. 

    ################################################################################
    # Name: RestoreSQLDB.ps1                                                       #
    # Version: 1.04                                                                #
    # Description: This Script Will Restore The Latest Express Full Backup Of      #
    # A Database Originating From A Standalone Or Clustered 'SQL Server A' To      #
    # A Standalone 'SQL Server B'.                                                 #
    # The Database Can Be Renamed On The Destination SQL Server.                   #
    # The File Paths Of The MDF & LDF Files Can Be Changed Too.                    #
    # The $PerformRestore Var Must Be Set To $true To Actually Perform The Restore #
    # This Script Requires DPM Remote Administration Tools (CLI) Are Installed.    #
    # Version Modifications:                                                       #
    #                                                                              #
    ################################################################################
    #To Sign This Script Run These Commands Separately From PS After Requesting A CodeSigning Certificate
    #$Certificate = Get-ChildItem cert:\CurrentUser\My -Codesign
    #Set-AuthenticodeSignature ".\RestoreSQLDB.ps1" -Certificate $Certificate
    #Example - Run From CMD Line
    #C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NonInteractive -NoLogo -File "<PATHTOFILE>\RestoreSQLDB.ps1"
    #Static Variables
        $DPMServer = "DPMSRV" #Name Of DPM Server Prtotecting SQL DB
        $ProtectionGroupSQLStr = "Protection Group SQL Server" #Name Of DPM Protection Group To Restore From
        $SourceServerName = "CLUSTER\TESTINST" #If Standalone SQL Then Should Be In The Format <SERVERNAME> Or <SERVERNAME>\<INSTANCENAME> If Named Instance
        $DestinationServerName = "DESTSRV" #Name Of Server That DB Will Be Restored To - If Named Instance Then Should Be In The Format <SERVERNAME>\<INSTANCENAME>
        $SQLDatabaseName = "Test_Backup" #Name Of DB To Restore
        $DestinationDatabaseName = "$SQLDatabaseName"+"_CC" #Name Of The Database That Will Be Created On $DestinationServerName
        $DestinationMDFPath = "E:\Data" #Check - Dependent On The Restore Server
        $DestinationLDFPath = "E:\Logs" #Check - Dependent On The Restore Server
        $PerformRestore = $true #$true To Actually Do Restore $false
        $LoggingLogName = "Windows PowerShell" #Event Log To Log Errors To
        $LoggingSource = "PowerShell" #Event Source To Log As
        $LoggingEventID = "65535" #Event Log ID To Use
    #It Begins!!!!
        Import-Module DataProtectionManager        
        Connect-DPMServer $DPMServer; #Conenct To DPM Server
    	
        #Get PG Object Named $ProtectionGroupSQLStr & Store As A Var
        $ProtectionGroupSQLObj = Get-ProtectionGroup $DPMServer | Where-Object { $_.FriendlyName -eq $ProtectionGroupSQLStr} 
        
        #Get DataSource With Name $SQLDatabaseName Running On Instance $SourceServerName & Store As A Var
        $DataSourceSQLObj = Get-DataSource -ProtectionGroup $ProtectionGroupSQLObj | Where-Object { $_.Name -eq $SQLDatabaseName -and $_.Instance -eq $SourceServerName} 
    	If ($DataSourceSQLObj -ne $null) #Only Continue If DB Exists
            { 
            #Add $DataSource As A SQLDataSource And Store As A Var
            $SQLDatabases = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$DataSourceSQLObj;
            #Get The Latest Disk Based (Express Full) Backup - Fails With Incremental Sync.
            $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.Location -eq "Disk"}  | Sort-Object BackupTime -Desc;
            
            If ($RecoveryPointsSQLObj.Count) #Check More Than 1 RP Is Returned
            { 
                $RecoveryPointToRestore = $RecoveryPointsSQLObj[0]; #Get The Latest RP (1st In List)
            } 
            Else #If Only 1 RP Is Returned
            { 
                $RecoveryPointToRestore = $RecoveryPointsSQLObj; 
            } 
            
            If ($RecoveryPointToRestore -eq $null) #If No RP's Are Returned...
            { 
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Failed. RP For DB: $SQLDatabaseName On: $SourceServerName Not Found In PG $ProtectionGroupSQLStr"
                Return 
            } 
                    
            $length = $RecoveryPointToRestore.PhysicalPath.Length; #Return Number Of Files (i.e. LDF And MDF Files) - 2 = 1x LDF and 1x MDF 
            
            #Setup The Alternative DB Object Ready For Restore - Create The Objects & Add As Many FileLocationMapping Placeholders As There Are Files To $AlternativeDatabaseObj
            $AlternativeDatabaseObj = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType; 
            $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length; 
            $AlternativeDatabaseObj.LocationMapping = $LocationMapping 
            
            $i = 0; #Resets The Count (See While Loop Below)
            While($i -lt $length) #Perform The While Loop While $i Is Less Than The Number Of Files To Restore ($length). Add The Crrent File Names And Locations For Each File To Be Restored 
            {        
                $AlternativeDatabaseObj.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping; #Create The Object
                $AlternativeDatabaseObj.LocationMapping[$i].FileName = $RecoveryPointToRestore.FileSpecifications[$i].FileSpecification; #Set File Name For Files
                $AlternativeDatabaseObj.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RecoveryPointToRestore.PhysicalPath[$i]); #Set Source Location (Path) For Files
                If ($AlternativeDatabaseObj.LocationMapping[$i].FileName.ToLower().EndsWith(".ldf")) #If LDF File Set Destination Location As $DestinationLDFPath
                { 
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationLDFPath 
                } 
                Else #If MDF File Set Destination Location As $DestinationMDFPath
                { 
                    $AlternativeDatabaseObj.LocationMapping[$i].DestinationLocation = $DestinationMDFPath 
                }        
                $i++; #Increment Counter (Move Onto Next File)
            } 
            $AlternativeDatabaseObj.InstanceName = $DestinationServerName;  #Set Destination Server Name. If Restoring To Named Instance Include The Instance Name 
            $AlternativeDatabaseObj.DatabaseName = $DestinationDatabaseName; #Set Destination DB Name
            
            #Create A Recovery Option Variable Targetted To The Destination Server, Set To Rename The DB And Use The $AlternativeDatabaseObj Details Created Earlier
            $ROP = New-RecoveryOption -TargetServer $DestinationServerName -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $AlternativeDatabaseObj; 
                
            #Load SQL SMO Class - Required To Check If DB Exists On $DestinationServerName
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null 
            #Create A New Object (SMO) Pass It The $DestinationServerName Variable And Store As A Var
            $SQLServerManagement = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server ($DestinationServerName)
            #If DB Exists At Destination, Write To Event Log
            If ($SQLServerManagement.databases[$DestinationDatabaseName] -ne $null)
            {
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Error: DB $DestinationDatabaseName Already Exists On $DestinationServerName - Restore Will Fail"
            }
                        
            If ($PerformRestore) #Only Run Restore If $PerformRestore Is $true
            { 
                $RestoreJob = Recover-RecoverableItem -RecoverableItem $RecoveryPointToRestore -RecoveryOption $ROP; #Start The Restore Operation Using The $ROP Recovery Option Var
                #The While Loop Below Effectively Pauses The Script Until The Succeeded Or Failed If Clauses Are Encountered. 
                $Wait = 2; #Initial Wait Time
                While ($RestoreJob -ne $null -and $RestoreJob.HasCompleted -eq $false) 
                { 
                    Start-Sleep -Seconds $Wait; 
                    $Wait = 20; 
                } 
                
                If($RestoreJob.Status -ne "Succeeded") #If Job Fails Write Appropriately To Event Log
                { 
                    Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Status: $($RestoreJob.Status)`n Start: $($RestoreJob.StartTime)`n  End: $($RestoreJob.EndTime)"
                } 
                Else #If Job Completes Write To Event Log
                { 
                    Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Restore Status: $($RestoreJob.Status)`n Start: $($RestoreJob.StartTime)`n  End: $($RestoreJob.EndTime)"
                } 
                
                $td = (New-Timespan -Start $RestoreJob.StartTime -end $RestoreJob.EndTime) #Calculate Time Taken To Restore & Write To Event Log
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Elapsed time: Hours: $($td.Hours) Minutes:$($td.Minutes) Seconds:$($td.Seconds) MSecs:$($td.Milliseconds)"
            } 
            Else #If $PerformRestore Is Set To $false Write To Event Log
            { 
                Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "PerformRestore Varible Is $false - Restore Will Not Happen"
            } 
        } 
        Else #If DB Doesnt Exist As DataSource In DPM Protection Group Write Error To Event Log
        { 
            Write-EventLog -LogName $LoggingLogName -Source $LoggingSource -EventID $LoggingEventID -Message "Database $SQLDatabaseName On $SourceServerName Does Not Exist. Nothing To Restore!"
        } 
        
        Disconnect-DPMServer $DPMServer #Disconnect DPM Server

    Cheers

    EDIT: Looks like the PS formatter here doesnt like escape characters ` - removed them
    EDIT: Finally found name of the other source for this script!

    • Edited by IT Badger Monday, June 3, 2013 11:12 AM More corrections!
    • Proposed as answer by IT Badger Monday, June 3, 2013 12:34 PM
    • Marked as answer by mannd75 Thursday, August 8, 2013 3:07 PM
    Monday, June 3, 2013 7:08 AM
  • Right after testing on a 2010 server this works bar the $RecoveryPointsSQLObj variable.

    You should change the variable above to :

    $RecoveryPointsSQLObj = Get-Recoverypoint -DataSource $SQLDatabases | Where-Object { $_.HasFastRecoveryMarker -eq "Fast" -and $_.IsRecoverable -and $_.DataLocation -eq "Disk"}  | Sort-Object BackupTime -Desc;

    Note the location property (either disk or media) in 2010 is DataLocation and not Location

    Other than that it should work fine ...does for me anyway

    Cheers

    • Marked as answer by mannd75 Thursday, August 8, 2013 3:07 PM
    Monday, June 3, 2013 10:39 AM
  • You are my hero.

    Your script worked without any modification using DPM 2012 SP1.

    Friday, July 12, 2013 10:14 PM
  • Thanks for the reply and sorry for not responding sooner, this dropped off the radar in the office.

    I've tried the script you suggested and I'm hitting an error.  I've tried it and it appears to work on DPM 2010 however we plan to upgrade to 2012 shortly and I can't get it to work on there either as the original script or the modified one??

    Any ideas?? 

    Thursday, August 8, 2013 2:45 PM
  • I think the fault may lie with my test DPM 2012 environment rather than the script.  It works 100% fine with the 2010 script, MANY THANKS :)

    I'll look into my test environment a little further and let you know if the 2012 script doesn't work.

    Thanks again.

    Dave

    Thursday, August 8, 2013 3:07 PM
  • 1 final question, how do I get the script to either overwrite the DB, or delete the existing before restoring.

    Thanks in advance.

    Friday, August 9, 2013 3:37 PM
  • This script is not working with named instances, did u try this script on named instances.

    please help.

    Tuesday, September 10, 2013 10:57 AM