none
DPM restore to alternate SQL server with Instance RRS feed

  • Question

  • Hello all,

    I'm having real trouble figuring out how to restore an SQL backup from one server to another SQL server (with a named instance) using Azure Backup Server.

    I am able to

    $PGroup = Get-DPMProtectionGroup -DPMServerName "DPM SERVER"
    $PObjects = Get-DPMDatasource -ProtectionGroup $PGroup[0] | Where-Object {$_.Name -eq 'DBNAME'}
    $RPoint = Get-DPMRecoveryPoint -Datasource $PObjects[0] | Sort -Property RepresentedPointInTime -Descending | Select-Object -First 1

    I get stuck on this line

    $ROption = New-DPMRecoveryOption

    Can someone help me figure out how to put this line together with the following variables?

    SQL1 = Original SQL Server

    SQL2 = New SQL Server for restore

    INSTANCE = Instance name on SQL2 (i.e. SQL2\INSTANCE)

    I want to restore it to the same DB name on the new server.

    Thank you

    M

    Tuesday, August 18, 2020 12:45 AM

Answers

  • Yes, this script is known to fail if you recover incremental backups specially when they are huge in number. So recovering full backups should be seamless.

    The link I shared initially, has another script created by a user, I think in that script, he has added the feature you just mentioned above. Give it a try and see if it helps, I have not tested it so cant comment on it.

    Glad to know that the script worked for you for full backups.

    (Don't forget to mark replies as answers if they were helpful)

    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Madasus1 Monday, August 31, 2020 4:38 PM
    Monday, August 31, 2020 3:45 PM

All replies

  • Hi,

    You can use this script to restore the SQL DBs to an alternate SQL instance. This script is created my Wilson Souza. Source: https://social.technet.microsoft.com/Forums/en-US/e5e50339-5707-4e72-bb9a-56f6d60ba926/restore-sql-2008-recovery-point-in-dpm-2010-to-second-sql-server-via-script?forum=dpmsqlbackup

    #                                                                                              
    # 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
    I have tested it in DPM 2019 and it works good. Try it and let me know if it worked for you. Thanks.


    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, August 18, 2020 9:04 AM
  • Thank you for your response.

    Oddly when I run the script I get all the prompts and then after answering the final prompt powershell just closes on me.

    Ideally I'd like to batch multiple database restores together

    I notice that some of your commandlets are a little different to mine - for example

    Your script: Restore-RecoverableItem

    Mine: Restore-DPMRecoverableItem

    Do you know what the difference is?

    Tuesday, August 18, 2020 12:42 PM
  • Hi,

    Restore-RecoverableItem -> This used to be in older version of DPM like 2007,2010. Now it is replaced with Restore-DPMRecoverableItem.

    You can replace Restore-RecoverableItem with Restore-DPMRecoverableItem and then run the script. Which DPM version are you running? And also, could you check if $restoreJob contains any information after the script exits on you?

    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, August 18, 2020 1:29 PM
  • As the powershell window completely closes i am unable to check what is contained in the variable.  It is not that the script exits but the whole window closes (and I am triggering the script from a command line(

    I'm running Azure Backup Server v13.0.580

    Tuesday, August 18, 2020 2:57 PM
  • Hi,

    Try running it on PowerShell ISE and then see if it still exits on you. You should be able to check each variable manually from ISE console. 

    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, August 18, 2020 3:15 PM
  • ISE also closes and reopens after the script runs....very odd

    Tuesday, August 18, 2020 5:37 PM
  • Do you have any other DPM/MABS server? If yes, run this script there and give the DPMservename manually when it prompts. 

    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, August 18, 2020 5:40 PM
  • no i don't.

    I'll modify your script into something with just the commands i need for a one-time restore and see if i can get it to work.  Will report back.

    Thursday, August 20, 2020 12:56 AM
  • So I figured out that this occurs when I restore an incremental.  If I restore a full i get no crash and the restore works fine.  Not sure why the incremental are screwing things up but I can survive without that

    Is there anyway to have the DPM restore offline the active database and do the restore if it is online or do I always need to go to SQL first and drop the databases before restoring? if I don't currently I get a VSS error that the files are in use

    Monday, August 31, 2020 3:21 PM
  • Yes, this script is known to fail if you recover incremental backups specially when they are huge in number. So recovering full backups should be seamless.

    The link I shared initially, has another script created by a user, I think in that script, he has added the feature you just mentioned above. Give it a try and see if it helps, I have not tested it so cant comment on it.

    Glad to know that the script worked for you for full backups.

    (Don't forget to mark replies as answers if they were helpful)

    Regards
    Aayoosh Moitro


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Madasus1 Monday, August 31, 2020 4:38 PM
    Monday, August 31, 2020 3:45 PM