none
Restore SQL 2008 Recovery Point in DPM 2010 to second SQL server via script

    Question

  • Hello all!

    I am attempting to automate the process to restoring a production SQL 2008 DB to a DEV environment, based on its recovery point time. Firstly, I am somewhat new to powershell and am unsure if this is even possible. It needs to restore daily. The script is necessary because the staff is small and management doesn't want to enable end-users to perform their own recoveries. Additionally, they do not want to manually execute the restore process as they want the recovery point to be restored at 3AM. Any input is greatly appreciated. 

    Thursday, April 21, 2011 2:02 PM

Answers

  • Here it goes....

    #                                                                                              
    # This script will allow you to restore the latest SQL recovery point databases from a single  
    # instance to a remote server                                                                  
    #                                                                                              
    # Author        : Wilson Souza                                                                 
    # Date Created  : 6/1/2011                                                                     
    # Last modified : 10/09/2012                                                                   
      $Version      = 1.3
      $ScriptName   = "Restore_SQL_to_Different_Instance"
    #                                                                                              
    # Change log                                                                                   
    # ==========                                                                                   
    #                                                                                              
    #       Ver 1.3 - If SQL Database exists and is in inactive protection, now the script will    
    #                 take that in consideration                                                   
    #       Ver 1.2 - Minor issues                                                                 
    #       Ver 1.1 - Add option to enter an alternate name for the database                       
    #                 If using Default Instance, just press ENTER instead of typing MSSQLSERVER    
    #                                                                                              
    # This version of the script was only tested on DPM 2010                                       
    #                                                                                              
     
    param ([string] $DPMServerName, [string[]] $DatabaseList, [Switch] $RollForwardRecovery)
     
    cls 
    write-host "$ScriptName Version $version`n"  -f white
    write-host "NOTE: " -foreground red -NoNewline
    write-host "Before continue, certify that the database you want to restore doesn't exist on the destination server" -foreground yellow
    write-host "      This script allows you to restore the database to the same drive letter of the original server" -foreground yellow
    write-host "      i.e. if the Protected Database is located on D drive, the server you are restoring the database to should have a D drive as well" -foreground yellow
    write-host "      This script was tested for stand alone SQL Servers (Not clustered databases)`n" -foreground yellow
     
    Add-Type -Path Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.dll
      
    if (!$DPMServerName)
    {
        $DPMServerName = Read-Host "DPM server name (ENTER = Local DPM server)"
        if (!$DPMServerName)
        {
            $DPMServerName = (&hostname)
        }
    }
    write-host $DPMServerName 
      
    if (!$DatabaseList)
    {
        $DatabaseList = Read-Host "`nEnter the SQL database to recover`n`nFormat: <SERVERNAME\<SQLINSTANCE>\<DATABASE_NAME> for named instance`n        <SERVERNAME\<DATABASE_NAME> for default instance "
        if (!$DatabaseList)
        {
            Write-Error "SQL database(s) not specified."
            exit 1
        }
    }
      
    if (!(Connect-DPMServer $DPMServerName))
    {
        Write-Error "Failed to connect To DPM server $DPMServerName"
        exit 1
    }
    write-host "`nChecking if $DatabaseList datasource exists..."  
    $datasourceList = @()
    Get-ProtectionGroup $DPMServerName | % {Get-Datasource $_} | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}
    get-datasource $DPMServerName -inactive | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}
    if ($datasourcelist.count -eq 0)
    {
        Write-host "Could not find datasource $DatabaseList in any protection group or inactive protection. Exiting..." -f red
        exit 1
    }
    $count = 1
    If ($datasourcelist.count -gt 1)
    {
    	write-host
    	write-host "     Protection Group                         DataSource Name"
    	write-host "     ---------------------------------------- ------------------------------------ " 
    	foreach ($datasourcelist1 in $datasourcelist)
    	{
    		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
    		write-host ("{0,2} - "-f $count) -foreground green -nonewline
            if ($datasourcelist1.CurrentProtectionState -ne 'Protected')
            {
                write-host ( "Inactive Protection" + " " * 22 + "{0,-36} " -f $datasourcelist1.LogicalPath) -f $color
            }
            else
            {
    		    write-host ( "{0,-40} {1,-36} " -f $datasourcelist1.ProtectionGroupName, $datasourcelist1.LogicalPath) -f $color
            }
    		$count++
    	}
        write-host
        write-host "Which datasource you want to restore: " -f green -nonewline
        [int]$data_to_restore = read-host
        if ($data_to_restore -lt 1 -or $data_to_restore -gt $datasourcelist.count)
        {
            write-host "Invalid entry. Exiting..."
            exit 1
        }
    }      
    $rp = @(Get-RecoveryPoint -Datasource $datasourcelist[$data_to_restore-1] | sort -Property RepresentedPointInTime -Descending)
    $count = 0
    write-host "`n" 
    foreach($rp1 in $rp)
    {
        write-host "["$count"] - " $rp1.RepresentedPointInTime
        $count++
    }
     
    $rp_to_restore = read-host "`nSelect the recovery point you want to restore (latest is on top)"
    $server_target1  = read-host "`nSelect the server you want to restore the database to"
    $instancename = read-host "`nWhat is the instance name (Enter for Default Instance)"
     
    $server_target = Get-ProductionServer $DPMServername | ? {$_.machinename -eq $server_target1}
    $FullInstanceName = "$($server_target1)\$($InstanceName)";
    $alternate_name = read-host "`nAlternate name for destination database. ENTER to use the same name"
     
    if ($instancename.length -eq 0 )
    {
        $fullinstancename = $server_target1
    }
    else
    {
        $FullInstanceName = $FullInstanceName.ToUpper()
    }
    $fullPIT = $rp[$rp_to_restore]
    $sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$datasourcelist[0];
    $i = 0;
    $length = $FULLPIT.PhysicalPath.Length;
     
    $alternateDatabaseDetails = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;
    $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;
    $alternateDatabaseDetails.LocationMapping = $LocationMapping
               
    while($i -lt $length)
    {
        $RPToBeUsed = $FULLPIT;
        $alternateDatabaseDetails.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping;
        $alternateDatabaseDetails.LocationMapping[$i].FileName = $RPToBeUsed.FileSpecifications[$i].FileSpecification;
        $alternateDatabaseDetails.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RPToBeUsed.PhysicalPath[$i]);
        $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $alternateDatabaseDetails.LocationMapping[$i].SourceLocation;
        $i++;
    }
     
    $alternateDatabaseDetails.InstanceName = $FullInstanceName;
     
    if ($alternate_name.length -eq 0)
    {
        $alternateDatabaseDetails.DatabaseName = $sqlDs.Name+"_PowerShell_Script";
    }
    else
    {
        $alternateDatabaseDetails.DatabaseName = $alternate_name;
    }
    $rop = New-RecoveryOption -TargetServer $server_target1 -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails;
    $restoreJob = Recover-RecoverableItem -RecoverableItem $FULLPIT -RecoveryOption $rop;
    write-host "Restore is in progress... you can check the recovery status from DPM 2010 Administrator Console, Monitoring/Jobs tab..." -foregroundcolor DarkGreen


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, October 28, 2011 7:36 AM
    Moderator

All replies

  • Here is the code:

    param ([string] $DPMServerName, [string[]] $DatabaseList, [Switch] $RollForwardRecovery)

    if(("-?","-help") -contains $args[0])
    {
        Write-Host "Description: This script restores the latest recovery point of the specified SQL databases."
        Write-Host "Usage: Recover-SqlDatabase.ps1 [-DPMServerName] <Name of the DPM server> [-DatabaseList] <Array of SQL databases to recover> [-RollForwardRecovery]"
        Write-Host "Example: Recover-SqlDatabase.ps1 mohitc02 `"mohitc04\* db`", `"mohitc04\reportservertempdb`""

        exit 0
    }

    if (!$DPMServerName)
    {
        $DPMServerName = Read-Host "DPM server name"

        if (!$DPMServerName)
        {
            Write-Error "Dpm server name not specified."
            exit 1
        }
    }

    if (!$DatabaseList)
    {
        $DatabaseList = Read-Host "SQL database to recover"

        if (!$DatabaseList)
        {
            Write-Error "SQL database(s) not specified."
            exit 1
        }
    }

    if (!(Connect-DPMServer $DPMServerName))
    {
        Write-Error "Failed to connect To DPM server $DPMServerName"
        exit 1
    }

    $datasourceList = @()
    Get-ProtectionGroup $DPMServerName | % {Get-Datasource $_} | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}

    # Show all the SQL databases that could not be found as protected datasources.
    foreach ($datasourceName in $DatabaseList)
    {
        if (@($datasourceList | ? {$_.LogicalPath -ieq $datasourceName}).Length -eq 0)
        {
            Write-Error "Could not find datasource $datasourceName"
        }
    }

    # Recover each SQL datasource from the latest recovery point.
    foreach ($datasource in $datasourceList)
    {
        # Select the latest recovery point that exists on disk and trigger the recovery job.
        foreach ($rp in @(Get-RecoveryPoint -Datasource $datasource | sort -Property RepresentedPointInTime -Descending))
        {
            foreach ($rsl in $rp.RecoverySourceLocations)
            {
                if ($rsl -is [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.ReplicaDataset])
                {
                    $recoveryOption = New-RecoveryOption -TargetServer $datasource.ProductionServerName -RecoveryLocation OriginalServer -SQL -RecoveryType Recover -RollForwardRecovery:$RollForwardRecovery
                    $recoveryJob = Recover-RecoverableItem -RecoverableItem $rp -RecoveryOption $recoveryOption -RecoveryPointLocation $rsl

                    break
                }
            }

            if ($recoveryJob)
            {
                break
            }
        }

        if ($recoveryJob)
        {
            Write-Host "`nRunning recovery for $($datasource.LogicalPath) from $($rp.RepresentedPointInTime)"

            # Comment out the next 7 lines to not wait for one recovery job to finish before triggering the next one.
            while (!$recoveryJob.HasCompleted)
            {
                Write-Host "." -NoNewLine
                sleep 3
            }

            Write-Host "`nJob status: $($recoveryJob.Status)"
        }
        else
        {
            Write-Error "Could not find a recovery point on disk for $($datasource.LogicalPath)"
        }
    }

    Thursday, April 21, 2011 11:59 PM
  • Hi SuMizzle,

    The PowerShell script above is the one you created and tried to use?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, May 20, 2011 9:05 PM
    Moderator
  • Yes.
    Saturday, May 21, 2011 12:21 AM
  • I see that this script performs a restore of the latest recovery point available for DB A on SERVER B to DB on SERVER B. In other words, it is restoring the DB ot its original location.

    You want to keep this configuration but be able to restore it to another SQL Server?

     

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Saturday, May 21, 2011 1:35 AM
    Moderator
  • Yes, but from a specific recovery point.
    Saturday, May 21, 2011 3:47 AM
  • Ok, so you are looking for two changes in that script:

     

    1. Allow to restore to an alternate SQL servr

    2. Select Which recovery point for that Database you want to restore


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Saturday, May 21, 2011 4:35 AM
    Moderator
  • Exactly! BTW, thank you for your time. It's greatly appreciated. 
    Saturday, May 21, 2011 5:30 AM
  • Hi Sumizzle,

    Here is the SQL Script...

    #------------ START COPYING FROM HERE ------------------------

    param ([string] $DPMServerName, [string[]] $DatabaseList, [Switch] $RollForwardRecovery)

    cls
    write-host "NOTE:`n" -foreground red
    write-host "Before continue, certify that the database you want to restore doesn't exist on the destination server" -foreground yellow
    write-host "This script allows you to restore data to the same drive letter of the original serve" -foreground yellow
    write-host "i.e. if the Protected Database is located on the D drive, the server you are restoring the database to should have a D drive as well" -foreground yellow
    write-host "This script was tested for stand alone SQL Servers (Not clustered databases)`n" -foreground yellow

    Add-Type -Path Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.dll
     
    if (!$DPMServerName)
     {
         $DPMServerName = Read-Host "DPM server name"
     
        if (!$DPMServerName)
         {
             Write-Error "Dpm server name not specified."
             exit 1
         }
     }
     
    if (!$DatabaseList)
     {

         $DatabaseList = Read-Host "`nEnter the SQL database to recover`n`nFormat: <SERVERNAME\<SQLINSTANCE>\<DATABASE_NAME> for named instance`n        <SERVERNAME\<DATABASE_NAME> for default instance "
     
        if (!$DatabaseList)
         {
             Write-Error "SQL database(s) not specified."
             exit 1
         }
     }
     
    if (!(Connect-DPMServer $DPMServerName))
     {
         Write-Error "Failed to connect To DPM server $DPMServerName"
         exit 1
     }
     
    $datasourceList = @()
    Get-ProtectionGroup $DPMServerName | % {Get-Datasource $_} | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}

         if ($datasourcelist.count -eq 0)
         {
             Write-Error "Could not find datasource $datasourceName"
         }
          $rp = Get-RecoveryPoint -Datasource $datasourcelist[0] | sort -Property RepresentedPointInTime -Descending


    $count = 0
    write host "`n"
    foreach($rp1 in $rp)
    {
      write-host "["$count"] - " $rp1.RepresentedPointInTime
     $count++
    }

    $rp_to_restore = read-host "`nSelect the recovery point you want to restore (latest is on top)"
    $server_target1  = read-host "`nSelect the server you want to restore the database to"
    $instancename = read-host "`nWhat is the instance name (Enter MSSQLSERVER for Default Instance)"

    $server_target = Get-ProductionServer $DPMServername | ? {$_.machinename -eq $server_target1}
    $FullInstanceName = "$($server_target1)\$($InstanceName)";

    if ($instancename -eq "MSSQLSERVER")
    {
     $fullinstancename = $server_target1
    }
    $FullInstanceName = $FullInstanceName.ToUpper()


    $fullPIT = $rp[$rp_to_restore]
    $sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$datasourcelist[0];
    $i = 0;
    $length = $FULLPIT.PhysicalPath.Length;

    $alternateDatabaseDetails = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;
    $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;
    $alternateDatabaseDetails.LocationMapping = $LocationMapping
             
    while($i -lt $length)
    {
        $RPToBeUsed = $FULLPIT;
        $alternateDatabaseDetails.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping;
        $alternateDatabaseDetails.LocationMapping[$i].FileName = $RPToBeUsed.FileSpecifications[$i].FileSpecification;
        $alternateDatabaseDetails.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RPToBeUsed.PhysicalPath[$i]);
        $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $alternateDatabaseDetails.LocationMapping[$i].SourceLocation;
        $i++;
    }

    $alternateDatabaseDetails.InstanceName = $FullInstanceName;
    $alternateDatabaseDetails.DatabaseName = $sqlDs.Name+"_PowerShell_Script";
    $rop = New-RecoveryOption -TargetServer $server_target1 -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails;
    $restoreJob = Recover-RecoverableItem -RecoverableItem $FULLPIT -RecoveryOption $rop;
    write-host "Restore is in progress... you can check the recovery status from DPM 2010 Administrator Console, Monitoring/Jobs tab..." -foregroundcolor DarkGreen

    #------------ STOP COPYING HERE ------------------------


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, June 1, 2011 8:06 AM
    Moderator
  • This script is very handy,

    But we want to do one more additional option;

    Namely restoring dbprod on nl-sql001 to dbtest on nl-sql002.

    Can you help us with this.


    Andre van den Berg
    Thursday, October 13, 2011 7:20 AM
  • Hi Andre,

    you are looking for a way to restore a DB named DBA from ServerA to ServerB but give an alternate name to DBA on the destination server, correct?

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, October 25, 2011 1:11 AM
    Moderator
  • Hi Wilson,

    Yes this is correct.


    Andre van den Berg
    Tuesday, October 25, 2011 6:54 AM
  • Here it goes....

    #                                                                                              
    # This script will allow you to restore the latest SQL recovery point databases from a single  
    # instance to a remote server                                                                  
    #                                                                                              
    # Author        : Wilson Souza                                                                 
    # Date Created  : 6/1/2011                                                                     
    # Last modified : 10/09/2012                                                                   
      $Version      = 1.3
      $ScriptName   = "Restore_SQL_to_Different_Instance"
    #                                                                                              
    # Change log                                                                                   
    # ==========                                                                                   
    #                                                                                              
    #       Ver 1.3 - If SQL Database exists and is in inactive protection, now the script will    
    #                 take that in consideration                                                   
    #       Ver 1.2 - Minor issues                                                                 
    #       Ver 1.1 - Add option to enter an alternate name for the database                       
    #                 If using Default Instance, just press ENTER instead of typing MSSQLSERVER    
    #                                                                                              
    # This version of the script was only tested on DPM 2010                                       
    #                                                                                              
     
    param ([string] $DPMServerName, [string[]] $DatabaseList, [Switch] $RollForwardRecovery)
     
    cls 
    write-host "$ScriptName Version $version`n"  -f white
    write-host "NOTE: " -foreground red -NoNewline
    write-host "Before continue, certify that the database you want to restore doesn't exist on the destination server" -foreground yellow
    write-host "      This script allows you to restore the database to the same drive letter of the original server" -foreground yellow
    write-host "      i.e. if the Protected Database is located on D drive, the server you are restoring the database to should have a D drive as well" -foreground yellow
    write-host "      This script was tested for stand alone SQL Servers (Not clustered databases)`n" -foreground yellow
     
    Add-Type -Path Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.dll
      
    if (!$DPMServerName)
    {
        $DPMServerName = Read-Host "DPM server name (ENTER = Local DPM server)"
        if (!$DPMServerName)
        {
            $DPMServerName = (&hostname)
        }
    }
    write-host $DPMServerName 
      
    if (!$DatabaseList)
    {
        $DatabaseList = Read-Host "`nEnter the SQL database to recover`n`nFormat: <SERVERNAME\<SQLINSTANCE>\<DATABASE_NAME> for named instance`n        <SERVERNAME\<DATABASE_NAME> for default instance "
        if (!$DatabaseList)
        {
            Write-Error "SQL database(s) not specified."
            exit 1
        }
    }
      
    if (!(Connect-DPMServer $DPMServerName))
    {
        Write-Error "Failed to connect To DPM server $DPMServerName"
        exit 1
    }
    write-host "`nChecking if $DatabaseList datasource exists..."  
    $datasourceList = @()
    Get-ProtectionGroup $DPMServerName | % {Get-Datasource $_} | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}
    get-datasource $DPMServerName -inactive | % {if ($DatabaseList -contains $_.LogicalPath) {$datasourceList += $_}}
    if ($datasourcelist.count -eq 0)
    {
        Write-host "Could not find datasource $DatabaseList in any protection group or inactive protection. Exiting..." -f red
        exit 1
    }
    $count = 1
    If ($datasourcelist.count -gt 1)
    {
    	write-host
    	write-host "     Protection Group                         DataSource Name"
    	write-host "     ---------------------------------------- ------------------------------------ " 
    	foreach ($datasourcelist1 in $datasourcelist)
    	{
    		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
    		write-host ("{0,2} - "-f $count) -foreground green -nonewline
            if ($datasourcelist1.CurrentProtectionState -ne 'Protected')
            {
                write-host ( "Inactive Protection" + " " * 22 + "{0,-36} " -f $datasourcelist1.LogicalPath) -f $color
            }
            else
            {
    		    write-host ( "{0,-40} {1,-36} " -f $datasourcelist1.ProtectionGroupName, $datasourcelist1.LogicalPath) -f $color
            }
    		$count++
    	}
        write-host
        write-host "Which datasource you want to restore: " -f green -nonewline
        [int]$data_to_restore = read-host
        if ($data_to_restore -lt 1 -or $data_to_restore -gt $datasourcelist.count)
        {
            write-host "Invalid entry. Exiting..."
            exit 1
        }
    }      
    $rp = @(Get-RecoveryPoint -Datasource $datasourcelist[$data_to_restore-1] | sort -Property RepresentedPointInTime -Descending)
    $count = 0
    write-host "`n" 
    foreach($rp1 in $rp)
    {
        write-host "["$count"] - " $rp1.RepresentedPointInTime
        $count++
    }
     
    $rp_to_restore = read-host "`nSelect the recovery point you want to restore (latest is on top)"
    $server_target1  = read-host "`nSelect the server you want to restore the database to"
    $instancename = read-host "`nWhat is the instance name (Enter for Default Instance)"
     
    $server_target = Get-ProductionServer $DPMServername | ? {$_.machinename -eq $server_target1}
    $FullInstanceName = "$($server_target1)\$($InstanceName)";
    $alternate_name = read-host "`nAlternate name for destination database. ENTER to use the same name"
     
    if ($instancename.length -eq 0 )
    {
        $fullinstancename = $server_target1
    }
    else
    {
        $FullInstanceName = $FullInstanceName.ToUpper()
    }
    $fullPIT = $rp[$rp_to_restore]
    $sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$datasourcelist[0];
    $i = 0;
    $length = $FULLPIT.PhysicalPath.Length;
     
    $alternateDatabaseDetails = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;
    $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;
    $alternateDatabaseDetails.LocationMapping = $LocationMapping
               
    while($i -lt $length)
    {
        $RPToBeUsed = $FULLPIT;
        $alternateDatabaseDetails.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping;
        $alternateDatabaseDetails.LocationMapping[$i].FileName = $RPToBeUsed.FileSpecifications[$i].FileSpecification;
        $alternateDatabaseDetails.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RPToBeUsed.PhysicalPath[$i]);
        $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $alternateDatabaseDetails.LocationMapping[$i].SourceLocation;
        $i++;
    }
     
    $alternateDatabaseDetails.InstanceName = $FullInstanceName;
     
    if ($alternate_name.length -eq 0)
    {
        $alternateDatabaseDetails.DatabaseName = $sqlDs.Name+"_PowerShell_Script";
    }
    else
    {
        $alternateDatabaseDetails.DatabaseName = $alternate_name;
    }
    $rop = New-RecoveryOption -TargetServer $server_target1 -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails;
    $restoreJob = Recover-RecoverableItem -RecoverableItem $FULLPIT -RecoveryOption $rop;
    write-host "Restore is in progress... you can check the recovery status from DPM 2010 Administrator Console, Monitoring/Jobs tab..." -foregroundcolor DarkGreen


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, October 28, 2011 7:36 AM
    Moderator
  • Hi Wilson,

    How are you doing.. I´m trying to use this great script in our DPM2010 + SQL environment.

    It´s working fine for the databases where I have the Protection Group configure to have 2 Express Full Backup each day and Sync just before the recovery point. (I have about 64 recovery points for these databases)

    For those databases where I have 1 Express Full Backup per day and sync every 15 minutes the script is crashing.
    ( I have about 3000 recovery points for these databases)

    Any ideas??

    Thanks,

    Joao Vitor Arruda


    Joao Vitor Arruda
    Tuesday, November 15, 2011 7:05 PM
  • Do you have the error on which point the script is failing?

    How many recovery points you have for SQL protection on the server where the script is NOT failing?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, November 15, 2011 7:11 PM
    Moderator
  • Wilson,

    Working and Failing databases are on the same server.

    The working ones are configured to have 2 Express Full Backup each day and Sync just before the recovery point. We have about 64 recovery points for these databases.

    The script is failing just before ask: "Alternate name for destination database...." (where it creates the recover job). Here is a screenshot of the problem.


    Joao Vitor Arruda
    Tuesday, November 15, 2011 7:19 PM
  • I think this has something to do with the amount of available recovery points...

    I'll revisit this.....


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, November 15, 2011 7:25 PM
    Moderator
  •  

    Very nice script work Wilson :)

     

    Is it possible to also input a different location and name for the DB files on the destination server ?

     

    Thanks,

    Jordan

    Friday, November 25, 2011 8:19 AM
  • CORRECTION

    The UI allows you to change the path/location but not the DB files' name.

    Changing the path/location works for you?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights

    Friday, November 25, 2011 8:29 AM
    Moderator
  •  

    I get the option to rename the DB or change the instance however I am not getting prompted to change the path. No errors are being returned it simply restores the db to the same path as the source (see below)

     

    Before continue, certify that the database you want to restore doesn't exist on
    the destination server
    This script allows you to restore data to the same drive letter of the original
    serve
    i.e. if the Protected Database is located on the D drive, the server you are res
    toring the database to should have a D drive as well
    This script was tested for stand alone SQL Servers (Not clustered databases)

    DPM server name: its-bcp-001

    Enter the SQL database to recover

    Format: <SERVERNAME\<SQLINSTANCE>\<DATABASE_NAME> for named instance
            <SERVERNAME\<DATABASE_NAME> for default instance : its-sql-001\model
    host


    [ 0] -  28/11/2011 12:01:27 AM
    [ 1] -  27/11/2011 12:01:19 AM
    [ 2] -  26/11/2011 12:01:27 AM
    [ 3] -  25/11/2011 12:01:26 AM
    [ 4] -  24/11/2011 12:01:26 AM

    Select the recovery point you want to restore (latest is on top): 0

    Select the server you want to restore the database to: ses-sql-004

    What is the instance name (Enter for Default Instance):

    Alternate name for destination database. ENTER to use the same name: modeltest
    tamanho 9
    Restore is in progress... you can check the recovery status from DPM 2010 Admini
    strator Console, Monitoring/Jobs tab...

     



    Monday, November 28, 2011 12:44 AM
  • correct, currently the path used is the same as the data source.

    I might be able to add an option to enter an alternate path for the restore

    Is this what you are looking for?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, November 28, 2011 12:53 AM
    Moderator
  •  

    Do you think it is possible (if not too difficult) to also rename the files during the restore ?

    The alternative is I simply do a "copy to folder" restore and then use other scripts to rename the files then attach them.

    The reason I ask is that we need this process to create clones of our production DB's for Dev and Testing and we often create multiple clones of the same DB hence the file name change is required.

     

     

    Monday, November 28, 2011 1:26 AM
  • Yes and no....

    If we use the restore to folder, then we can rename the files AFTER the restore is done and before using some other script to attach the database to SQL


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, November 28, 2011 2:00 AM
    Moderator
  •  

    That's what I am thinking at this stage.

    Restore to folder, a different script to move and rename the files then a SQL script to attach the database...

    Monday, November 28, 2011 2:03 AM
  • I tried your script on my systems Wilson...works great for databases on simple recovery mode. It fails when you try to restore an incremental backup. My script is based on your wonderful work and is not as flexible as yours - I specifically wanted to recover the latest non-incremental backup of live data to a dev server with a default instance of SQL.

    It asks for the DPM server name, the name of the protection group containing DBs to restore, and the target server. My dev servers all have the same disk letters layout as the live server as required.

    Here it is...

    Add-Type -Path Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.dll

    $DPMServer = read-host "Type the DPM server name"
    $ProtectionGroupName = read-host "Type the Protection Group name (as seen in the GUI)"

    $TargetServerName = Read-Host "Type the server you want to restore to"
    $TargetServer = Get-ProductionServer $DPMServer | where {$_.machinename -eq $TargetServerName}

    $ProtectionGroup = Get-ProtectionGroup -DPMServerName $DPMServer | where {$_.FriendlyName -eq $ProtectionGroupName}
    $DataSource = Get-Datasource -ProtectionGroup $ProtectionGroup
    write-host

    ForEach ($Item in $DataSource)
    {
     If (($Item.Name -ne "model") -and (($Item.name -ne "msdb") -and ($Item.name -ne "master")))
     {
      $RP = Get-RecoveryPoint -DataSource $Item | sort -Property RepresentedPointInTime -Descending
      $sqlDs = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.SQLDataSource]$Item;
      $j = 0
       while($RP[$j].IsIncremental -eq 'true')
      {
       $j++
      }
      $i = 0;
       $length = $RP[$j].PhysicalPath.Length;
     
      $alternateDatabaseDetails = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.AlternateDatabaseDetailsType;
       $LocationMapping = New-Object Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping[] $length;
       $alternateDatabaseDetails.LocationMapping = $LocationMapping
               
      while($i -lt $length)
       {
            $alternateDatabaseDetails.LocationMapping[$i] = New-Object -TypeName Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.SQL.FileLocationMapping;
            $alternateDatabaseDetails.LocationMapping[$i].FileName = $RP[$j].FileSpecifications[$i].FileSpecification;
            $alternateDatabaseDetails.LocationMapping[$i].SourceLocation = [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.PathHelper]::GetParentDirectory($RP[$j].PhysicalPath[$i]);
            $alternateDatabaseDetails.LocationMapping[$i].DestinationLocation = $alternateDatabaseDetails.LocationMapping[$i].SourceLocation;
            $i++;
      }
     
      $alternateDatabaseDetails.InstanceName = $TargetServerName
       $alternateDatabaseDetails.DatabaseName = $sqlDs.Name
      $rop = New-RecoveryOption -TargetServer $TargetServerName -RecoveryLocation OriginalServerWithDBRename -SQL -RecoveryType Recover -AlternateDatabaseDetails $alternateDatabaseDetails;
      $restoreJob = Recover-RecoverableItem -RecoverableItem $RP[$j] -RecoveryOption $rop;
      write-host $sqlds.name "restore is in progress" -NoNewLine
      while (! $restoreJob.hasCompleted )
          {
       Write-Host "." -NoNewLine
              Start-Sleep 5
          }
      write-host ".complete"
     }
    }

     


    Monday, December 5, 2011 2:22 PM
  • Thanks for your work on this - this is awesome - the only example of its kind that I have found.

    Some questions:

    1. Is there a way to overwrite existing databases (right now it fails if the database already exists)

    2. How did you come up with the script (what values to fill for alternateDatabaseDetails, etc - especially because there is no help available on technet or elsewhere?

    Thanks again


    http://blog.aggregatedIntelligence.com/
    Wednesday, January 18, 2012 11:53 PM
  • Hi Rocky,

    1. Currently no. The script takes in consideration that the destination database doesn't exist. If you want to overwrite an existing DB there is a need to introduce the logic to analyze if the DB with that name exists and delete prior attaching the restored version.

    2. You can find mostly of the information in TechNet for PowerShell commands. They don't have much real life usage example though so I spent lot time figuring all of this out...

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, January 19, 2012 12:02 AM
    Moderator
  • I dont know how you figured that you could use " OriginalServerWithDBRename " to restore to a different database-server - but thats awesome!
    http://blog.aggregatedIntelligence.com/
    Thursday, January 19, 2012 12:11 AM
  • If you type in the command below from DPM Shell you can get the valid values for -RecoveryLocation and one of these values is OriginalServerWithDBRename

    help New-RecoveryOption -full
    

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, January 19, 2012 12:39 AM
    Moderator
  • Here is my revisions to Wilson's script:  http://blog.aggregatedintelligence.com/2012/01/dpmrestoring-protected-sqlserver.html

    It adds the functionality to drop an existing database on the destination db server. Also allows you to change the location of the mdf and log files when its restored to the destination server.

    Thanks again Wilson


    http://blog.aggregatedIntelligence.com/
    Monday, January 23, 2012 6:53 PM
  • did you manage to resolve this issue?  I'm getting the same error when a large number of recovery points are involved.  But works when only a small number.

    Thanks

    Friday, May 4, 2012 4:23 PM
  • Interesting thread: Wilson Souza Rev 1.3, NeilRawlinson recovering of the latest non-incremental backup of live data  and rockyMtnRajah revision

      



    If this post has been useful please click the green arrow to the left or click "Propose as answer"

    Friday, February 15, 2013 11:19 AM