none
SQL Restore to alternate instance RRS feed

  • Question

  • Hello,

     

    I'm having some trouble with a PowerShell script.

    The goal is to be able to run the script and have it create a fresh recovery point for the selected protected live database and restore it to a staging SQL server for testing deployment.  I'm able to restore the database MDF and LDF files to the staging server via the filesystem, but I'm having trouble getting the direct to SQL restore (a la the UI) working properly.  Below is the PowerShell script as it stands now.

    Any advice would be greatly appreciated.

     

    Thanks

     

    if (!(Get-PSSnapin Microsoft.DataProtectionManager.PowerShell))
    {
      Write-Host "Adding PS Snapin for DPM..." -Fore Green
      Add-PSSnapin Microsoft.DataProtectionManager.PowerShell
    }
    
    $dpmServerName = "dpmServer"
    $sqlProtectionGroupName = "SQL Protection Group"
    $sqlProductionServerName = "productionServer"
    $stagingServerName = "stagingServer"
    #on the staging server, the database files all live in the default location
    $stagingPath = "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"
    
    $sqlProtectionGroup = Get-ProtectionGroup -DPMServerName $dpmServerName | Where-Object { $_.FriendlyName -eq $sqlProtectionGroupName }
    $sqlDatasources = Get-Datasource -ProtectionGroup $sqlProtectionGroup | Where-Object { $_.Instance -eq $sqlProductionServerName }
    
    #Prompt for database
    Write-Host "Production Databases:"
    $dbCount = 0
    foreach ($item in $sqlDatasource)
    {
      $dbCount++
      Write-Host ("  $dbCount) " + $item.DisplayPath)
    }
    
    $itemNumber = Read-Host "Database number to deploy"
    $recoveryDatasource = $sqlDatasources[$itemNumber - 1]
    Write-Host "Creating recovery point..." -NoNewline
    $recoveryPointStatus = New-RecoveryPoint -Datasource $recoveryDataSource -Disk -BackupType ExpressFull -WithDataIntegrityCheck
    
    while ($recoveryPointStatus.HasCompleted -eq $False)
    {
      Write-Host "." -NoNewline
      Start-Sleep -Seconds 1
    }
    
    if ($recoveryPointStatus.HasCompleted -eq $False -or $recoveryPointStatus.Status -ne "Succeeded")
    {
      #Handle recovery point creation error
      Write-Error "Failure creating recovery point."
      exit 1
    }
    
    Write-Host "Done."
    
    #Find the latest recoverable point for the datasource and restore it to disk. This should be the point we just created.
    foreach ($recoveryPoint in (Get-RecoveryPoint -Datasource $recoveryDatasource | sort -Property RepresentedPointInTime -Descending))
    {
      foreach ($recoverySourceLocation in $recoveryPoint.RecoverySourceLocations)
      {
        if ($recoverySourceLocation -is [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.OMCommon.ReplicaDataset])
        {
          #Filesystem restore (works fine, except the MDF and LDF files wind up buried in a series of subdirectories representing their
          #location on the production server filesystem. I'm not too concerned with that, as this isn't what I want to do anyway.
          $recoveryOption = New-RecoveryOption -SQL -TargetServer $stagingServerName -RecoveryLocation CopyToFolder -RecoveryType Restore -TargetLocation $stagingPath
          $restoreJob = Recover-RecoverableItem -RecoverableItem $recoveryPoint -RecoveryOption $recoveryOption -RecoveryPointLocation $recoverySourceLocation
    
          #New-RecoveryOption [-TargetServer] [-RecoveryLocation] -RecoveryType -SQL [-AlternateDatabaseDetails] [-CopyLogFiles] [-DPMLibrary] [-LeaveDBInRestoringState] [-LogFileCopyLocation] 
          #          [-RecoverToReplicaFromTape] [-RestoreSecurity] [-RollForwardRecovery] [-SANRecovery] [-TargetLocation] [<CommonParameters>]
    
          #SQL Restore
          #$recoveryOption = New-RecoveryOption -SQL -AlternateDatabase 
          #$restoreJob = Recover-RecoverableItem -RecoverableItem $recoveryPoint -RecoveryOption $recoveryOption -RecoveryPointLocation $recoverySourceLocation
    
          break
        }
      }
    
      if ($restoreJob)
      {
        break
      }
    }
    
    

     

    Wednesday, October 27, 2010 7:37 PM

Answers

  • Hi,

    Indeed there is no CMDLET parameter set that does this in all.
    Restoring to alternate instance is a combination of restore the files (you already succeed with) and mounting the data base using SQLDMO or whatever you prefer. The UI does similar.

     


    \R2 This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, November 25, 2010 8:10 AM

All replies

  • I've still not had any luck with this, and I'm starting to think that SQL restore to alternate instances is not supported through the PowerShell snapin.  Is there any way to get "official" word on this?

    As an alternate option, I've been playing around with the SQL Self-Service recovery tool, but I seem to be running into other limitations with that.

    1)  When restoring to an alternate SQL instance, it fails if the database already exists on that instance.  My goal is to overwrite any existing database.  Technet does say that restoring to the original instance is not supported with the SSRT, but this is an alternate instance.

    2)  If overwriting the database would work, DPM always inserts a DPM_datetime folder into the path, so the actual MDF and LDF files don't get replaced.  This could lead to a huge buildup of data files and eventually eat up all the drive space.

     

    I understand it's possible that I'm trying to use DPM for something it wasn't designed for.  However, it would be a great solution.  Our production databases are at a co-location, while the DPM server is with our development servers.  Restoring from DPM is extremely fast, whereas copying a backup from the live servers can take hours over the WAN.

     

    Thanks in advance for any suggestions or assistance.

    Tuesday, November 2, 2010 5:08 PM
  • Hi,

    Indeed there is no CMDLET parameter set that does this in all.
    Restoring to alternate instance is a combination of restore the files (you already succeed with) and mounting the data base using SQLDMO or whatever you prefer. The UI does similar.

     


    \R2 This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, November 25, 2010 8:10 AM
  • @Ruud

    Why? DPM has already that ability. Why should I look for extra cmdlets while I can do same job from GUI? That's the reason why Microsoft produced Powershell, combining complex commands and serving them as single command. Adding that feature to the shell, shouldn't be hard (at least for Microsoft)

    I don't even understand why there is no Powershell output on DPM 2007 or DPM 2010. I haven't tested DPM 2012 yet, but I hope we have more flexible Powershell experience with it.

    Yusuf.


    MSP - Microsoft Student Partner (2009-2010) http://www.yusufozturk.info
    Friday, March 25, 2011 1:29 PM