ps_RestoreGene

Save this script as ps_RestoreGene.ps1 then call passing parameters to suit purpose, no changes should be necessary.

Copy / Paste only works with Chrome or newer versions of Internet Explorer. A  line feed/character return problem corrupts the script if copied using Internet Explorer 8 or lower.


<#--------------------------------------------------------------------------
.SYNOPSIS
  
Restore Generator v6.57 (2016-11-19)
(C) 2012, Paul Brewer
                      
Feedback: paulbrewer@yahoo.co.uk
Updates: https://paulbrewer.wordpress.com/ps_restoregene/
User Guide: https://paulbrewer.wordpress.com/2016/08/05/restoregeneversion6/
     
                     
.DESCRIPTION
Calls stored procedure sp_RestoreGene on the Primary server to get a RESTORE script which is executed on the Standby server
Updates: https://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/
                     
V3.5 - geostock - Updated to include a 'ConsoleFeedback' parameter, defaulted to on. Change to off to suppressed write-host in SQL Agent jobs.
V3.6 - Richard - Variable declare fix, where database name has a space or full stop.
     - EXIT this script if there are no new log backups to restore, don't THROW
V3.9 - SQLMongo fix for hyphen in database name.
V4.0 - Domingo fix for default backup paths
V5.0 - Change default for WithStandby to 0, NoRecovery is the default
V5.1 - Mick Pollock Azure URL backup path support and string replace functionality
V5.2 - June 17th, 2016 - Add DebugMode parameter, writes restore script to xml log file, does not execute commands
V5.4 - July 3rd, 2016 - New parameter for FILESTREAM files, for WITH MOVE parameter, jgkiran
V5.8 - WITH MOVE secondary file names no longer included as separate lines, code tidying modification
     - Requires Version 5.8 or higher of the stored procedure sp_RestoreGene
V6.0 - Improved error handling / feedback
V6.1 - Add Throw on Error
V6.4 - Publish SQL Server Central
V6.41 - PoSh Driver Last LSN minor fix.
V6.44 - Kill connections to Target DB Name if supplied, Bug fix identified by Rod.
V6.5  - New 'Drop Database After Restore' parameter for use with CHECKDB and automated backup verification, John Lee
V6.54 - New Parameter to exclude differential and log backups, for development environment refreshes, Mehedi Amin
V6.57 - New parameters to exclude backup device types, remove variable declares/RAISEERROR, Lars Rasmussen 
V8.04 - April 15th.2018 - Error handling, checking pending restore commands, Thomas Wolf
                     
---------------------------------------------------------------------------#>
#Script Parameters
Param(
                     
        #Name and path for the restore log
        [Parameter(Mandatory=$true)]
        $RestoreLog,
                     
        #Primary server, SQL instance
        [Parameter(Mandatory=$true)]
        $PrimaryServer,
                     
        #Standby server, SQL instance
        [Parameter(Mandatory=$true)]
        $StandbyServer,
                     
        #DBName server, SQL instance
        [Parameter(Mandatory=$true)]
        $DBName,
                     
        #The TargetDBName can override of the restored db name, defaults to DBName is needed.
        $TargetDBName = $null,
                     
        #The WithReplace parameter is needed if over-wrtting the source of the database backup (PrimaryServer=StandbyServer)
        # and if no tail log backup was taken.
        $WithReplace = "0",
                     
        #The WithMoveDataFiles parameter allows the restored database to use a different path for database files.
        $WithMoveDataFiles = $null,
                     
        #The WithMoveLogFile parameter allows the restored database to use a different path for its log file.
        $WithMoveLogFile = $null,
                     
        #The WithMoveFileStreamFile parameter allows the restored database to use a different path for its FileStream files.
        $WithMoveFileStreamFile = $null,
                     
        #The FromFileFullUNC parameter allows overriding the drive & folder path of the full backup files, possibly with network share name.
        $FromFileFullUNC = $null,
                     
        #The FromFileDiffUNC parameter allows overriding the drive & folder path of the diff backup files, possibly with network share name.
        $FromFileDiffUNC = $null,
                     
        #The FromFileLogUNC parameter allows overriding the drive & folder path of the log backup files, possibly with network share name.
        $FromFileLogUNC = $null,
                     
        #The StopAt parameter allows overriding the date / time recovery point to historic backup files, defaults to most current.
        $StopAt = $null,
                     
        #The StandBy parameter allows overriding WITH STANDBY so database is readable.
        $StandBy = "0",
                     
        #The WithRecovery parameter allows overriding WITH RECOVERY, default is NORECOVERY .
        $WithRecovery = "0",
                     
        #The WithCHECKDB parameter executes CHECKDB, only possible in conjunction WITH RECOVERY .
        $WithCHECKDB = "0",
                     
        #The LogShippingInitialize parameter performs a full, diff, log(s) recovery if 1, else outstanding logds only if 1
        $LogShippingInitialize = "1",
                     
        # Removed in V6.57, causes issues in edge cases
        #The Log_Reference parameter value is written to the SQL Error Log
        #$Log_Reference = $null,
                     
        #The KillConnections parameter will kill restore blocking SPID's if 1
        $KillConnections = "1",
                     
        #If run interactively, change to "0" for SQL Agent Jobs.
        $ConsoleFeedback = "1",
               
        # Credentials for Azure Blog Storage
        $BlobCredential = $null,
               
        # RestoreScript String Find
        $RestoreScriptReplaceThis = $null,
               
        # Restore Script String Replace
        $RestoreScriptWithThis = $null,
              
        # Log restore commands, do not execute
        $DebugMode = $null,
  
        # Drop the database after the restore
        $DropDatabaseAfterRestore = $null,
  
        # Drop the database after the restore
        $ExcludeDiffAndLogBackups = $null,
  
        # Include backup device type "7"
        $IncludeDeviceType7 = "1",
  
        # Include backup device type "102"
        $IncludeDeviceType102 = "1",
  
        # Include backup device type "2"
        $IncludeDeviceType2 = "1",
  
        # Include backup device type "9"
        $IncludeDeviceType9 = "1"    
                     
   )
                     
# Defaults and Overrides
if ($FromFileDiffUNC -eq $null) {$FromFileDiffUNC = $FromFileFullUNC}
if ($FromFileLogUNC-eq $null) {$FromFileLogUNC= $FromFileFullUNC}
if ($StopAt -eq $null) {$StopAt = Get-Date -Format s}
              
# ==============================================================================
# Open a connection to the primary server
$SQLConnectionPrimary = New-Object System.Data.SqlClient.SqlConnection
try
{
    $SQLConnectionPrimary.ConnectionString = "Server=" + $PrimaryServer + ";Database=master;Integrated Security=True"
    $SQLConnectionPrimary.Open()
}
catch
{
    throw "Error : Connection to Primary server cannot be established"
}
                     
# ==============================================================================
# Open a connection to the standby server,to execute RESTORE commands
$SQLConnectionStandby = New-Object System.Data.SqlClient.SqlConnection
try
{
    $SQLConnectionStandby.ConnectionString = "Server=" + $StandbyServer + ";Database=master;Integrated Security=True"
    $SQLConnectionStandby.Open()
}
catch
{
    throw "Error : Connection to Standby server cannot be established"
}
                     
# ==============================================================================
# Check for connections blocking the restore on the standby server database
if ($KillConnections -eq 0)
{
  if ($TargetDBName -eq $null) {$activeconnections = "SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('" + $DBName + "')"}
  elseif ($TargetDBName -ne $null) {$activeconnections = "SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('" + $TargetDBName + "')"}
    
  $check = Invoke-Sqlcmd -Query $activeconnections -Database "master" -ServerInstance $StandbyServer
  if ($check.spid -ne $null)
  {throw "Error : Active connections to the database are blocking the restore on the standby server"}
}
                     
# ==============================================================================
#  Function to kill restore blocking SPID's on standby
function f_killconnections
{
  if ($TargetDBName -eq $null) {$KillQueryConstructor = "SELECT ';KILL ' + CAST(spid AS VARCHAR(4)) + '' FROM sys.sysprocesses WHERE spid > 50 AND dbid = DB_ID('" + $DBName + "')"}
  elseif ($TargetDBName -ne $null) {$KillQueryConstructor = "SELECT ';KILL ' + CAST(spid AS VARCHAR(4)) + '' FROM sys.sysprocesses WHERE spid > 50 AND dbid = DB_ID('" + $TargetDBName + "')"}
    
  $KillCommands = Invoke-Sqlcmd  -QueryTimeout 6000 -Query $KillQueryConstructor -Database "master" -ServerInstance $StandbyServer
  foreach ($KillCommand in $KillCommands)
  {
    if ($KillCommand -ne $NULL)
    {
      $KillCommand[0] | Out-Default
      $Result = Invoke-Sqlcmd -Query $KillCommand[0] -Database "master" -ServerInstance $StandbyServer
      $Result | Out-Default
    }
  }
}        
                     
# ==============================================================================
# Get Previous Restore Log details
$StartTime = Get-Date -Format s
                     
if ($LogShippingInitialize -eq "1") {$LogShippingStartTime = $StartTime}
                     
if ($LogShippingInitialize -eq "0")
{
  [xml]$PreviousLog = Get-Content -Path $RestoreLog
  $LogShippingStartTime = $PreviousLog.LogData.LogShippingStartTime.Description
  $LogShippingLastLSN = $PreviousLog.LogData.LogShippingLastLSN.Description
  $PreviousWithRecovery = $PreviousLog.LogData.WithRecovery.Description
  if ($LogShippingLastLSN -eq $null) {throw "Error : Previous log file LastLSN is invalid or not found"}
  if ($PreviousWithRecovery -eq "1") {throw "Error : WITH RECOVERY has been run on the standby server"}
}
                     
# ==============================================================================
# Restore Log File processing, record start runtime details
# Create a new XML File with  root node
[System.XML.XMLDocument]$oXMLDocument=New-Object System.XML.XMLDocument 
                     
# New Node
[System.XML.XMLElement]$oXMLRoot=$oXMLDocument.CreateElement("LogData") 
                     
# Append as child to an existing node
$oXMLDocument.appendChild($oXMLRoot) 
                     
# Add a Attribute
$oXMLRoot.SetAttribute("description","ps_LogShippingLight") 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("StartTime"))
$oXMLSystem.SetAttribute("Description",$StartTime) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("WithRecovery"))
$oXMLSystem.SetAttribute("Description",$WithRecovery) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("DBName"))
$oXMLSystem.SetAttribute("Description",$DBName) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("TargetDBName"))
$oXMLSystem.SetAttribute("Description",$TargetDBName) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("WithMoveDataFiles"))
$oXMLSystem.SetAttribute("Description",$WithMoveDataFiles) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("WithMoveLogFiles"))
$oXMLSystem.SetAttribute("Description",$WithMoveLogFile) 
           
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("WithMoveFileStreamFile"))
$oXMLSystem.SetAttribute("Description",$WithMoveFileStreamFile) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("FromFileFullUNC"))
$oXMLSystem.SetAttribute("Description",$FromFileFullUNC) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("FromFileDiffUNC"))
$oXMLSystem.SetAttribute("Description",$FromFileDiffUNC) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("FromFileLogUNC"))
$oXMLSystem.SetAttribute("Description",$FromFileLogUNC) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("StopAt"))
$oXMLSystem.SetAttribute("Description",$StopAt) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("LogShippingStartTime"))
$oXMLSystem.SetAttribute("Description",$LogShippingStartTime) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("Standby"))
$oXMLSystem.SetAttribute("Description",$StandBy) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("WithCHECKDB"))
$oXMLSystem.SetAttribute("Description",$WithCHECKDB) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("PrimaryServer"))
$oXMLSystem.SetAttribute("Description",$PrimaryServer) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("StandbyServer"))
$oXMLSystem.SetAttribute("Description",$StandbyServer) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("RestoreLog"))
$oXMLSystem.SetAttribute("Description",$RestoreLog) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("LogShippingInitialize"))
$oXMLSystem.SetAttribute("Description",$LogShippingInitialize ) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("KillConnections"))
$oXMLSystem.SetAttribute("Description",$KillConnections) 
               
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("BlobCredential"))
$oXMLSystem.SetAttribute("Description",$BlobCredential) 
               
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("RestoreScriptReplaceThis"))
$oXMLSystem.SetAttribute("Description",$RestoreScriptReplaceThis) 
               
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("RestoreScriptWithThis"))
$oXMLSystem.SetAttribute("Description",$RestoreScriptWithThis)
              
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("DebugLog"))
$oXMLSystem.SetAttribute("Description",$DebugLog)
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("DropDatabaseAfterRestore"))
$oXMLSystem.SetAttribute("Description",$DropDatabaseAfterRestore)
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("ExcludeDiffAndLogBackups"))
$oXMLSystem.SetAttribute("Description",$ExcludeDiffAndLogBackups)             
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("IncludeDeviceType7"))
$oXMLSystem.SetAttribute("Description",$IncludeDeviceType7) 
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("IncludeDeviceType102"))
$oXMLSystem.SetAttribute("Description",$IncludeDeviceType102) 
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("IncludeDeviceType2"))
$oXMLSystem.SetAttribute("Description",$IncludeDeviceType2) 
  
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("IncludeDeviceType9"))
$oXMLSystem.SetAttribute("Description",$IncludeDeviceType9) 
  
                     
# ==============================================================================
#Snapin for the Invoke-SQLCmd cmdlet
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Import-Module SQLPS -DisableNameChecking
$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
Set-Location -Path $dir
                     
# ==============================================================================
# Construct the Restore Gene stored procedure call
              
if ($LogShippingInitialize  -eq "0") 
{
  $LogShippingLight_EXEC = "EXEC dbo.sp_RestoreGene @LogShippingLastLSN = '" + $LogShippingLastLSN + "', @LogShippingStartTime= '" + $LogShippingStartTime + "',@Database = '" + $DBName + "', @TargetDatabase = '" + $TargetDBName + "',@WithMoveDataFiles = '" + $WithMoveDataFiles + "', @WithMoveLogFile = '" + $WithMoveLogFile + "', @WithMoveFileStreamFile = '" + $WithMoveFileStreamFile + "', @FromFileFullUNC = '" + $FromFileFullUNC + "', @FromFileDiffUNC = '" + $FromFileDiffUNC + "', @FromFileLogUNC= '" + $FromFileLogUNC+ "', @StopAt = '" + $StopAt + "', @StandbyMode = '" + $Standby + "', @WithReplace = '" + $WithReplace + "' , @WithRecovery = '" + $WithRecovery + "', @WithCHECKDB = '" + $WithCHECKDB + "', @DropDatabaseAfterRestore = '" + $DropDatabaseAfterRestore + "' , @BlobCredential = '" + $BlobCredential + "', @RestoreScriptReplaceThis = '" + $RestoreScriptReplaceThis + "', @RestoreScriptWithThis = '" + $RestoreScriptWithThis + "'" + ", @SetSingleUser = '" + $KillConnections + "'"  + ", @ExcludeDiffAndLogBackups = '" + $ExcludeDiffAndLogBackups + "'"  + ", @IncludeDeviceType7 = '" + $IncludeDeviceType7 + "'"  + ", @IncludeDeviceType102 = '" + $IncludeDeviceType102 + "'"  + ", @IncludeDeviceType2 = '" + $IncludeDeviceType2 + "'"  + ", @IncludeDeviceType9 = '" + $IncludeDeviceType9 + "'"
}
else
{
  $LogShippingLight_EXEC = "EXEC dbo.sp_RestoreGene @Database = '" + $DBName + "', @TargetDatabase = '" + $TargetDBName + "',@WithMoveDataFiles = '" + $WithMoveDataFiles + "', @WithMoveLogFile = '" + $WithMoveLogFile + "', @WithMoveFileStreamFile = '" + $WithMoveFileStreamFile + "', @FromFileFullUNC = '" + $FromFileFullUNC + "', @FromFileDiffUNC = '" + $FromFileDiffUNC + "', @FromFileLogUNC= '" + $FromFileLogUNC+ "', @StopAt = '" + $StopAt + "', @StandbyMode = '" + $Standby + "', @WithReplace = '" + $WithReplace + "' , @WithRecovery = '" + $WithRecovery + "', @WithCHECKDB = '" + $WithCHECKDB + "', @DropDatabaseAfterRestore = '" + $DropDatabaseAfterRestore + "' , @BlobCredential = '" + $BlobCredential + "', @RestoreScriptReplaceThis = '" + $RestoreScriptReplaceThis + "', @RestoreScriptWithThis = '" + $RestoreScriptWithThis + "'" + ", @SetSingleUser = '" + $KillConnections + "'"  + ", @ExcludeDiffAndLogBackups = '" + $ExcludeDiffAndLogBackups + "'"  + ", @IncludeDeviceType7 = '" + $IncludeDeviceType7 + "'"  + ", @IncludeDeviceType102 = '" + $IncludeDeviceType102 + "'"  + ", @IncludeDeviceType2 = '" + $IncludeDeviceType2 + "'"  + ", @IncludeDeviceType9 = '" + $IncludeDeviceType9 + "'"
}
                  
if ($ConsoleFeedback -eq "1") {
  Write-Host "-----------------------------------------"
  Write-Host "RestoreGene Batch Execution Starting"                  
  Write-Host "-----------------------------------------"
  Write-Host $LogShippingLight_EXEC
}
              
# ==============================================================================
# Execute the sp_RestoreGene stored procedure on the primary server
try
{
    $LogShippingLight_Results = Invoke-SQLCmd -Query $LogShippingLight_EXEC -QueryTimeout 6000 -Database "master" -ServerInstance $PrimaryServer
}
catch
{             
    if ($LogShippingLight_Results -eq $null)
    {
        #throw "Error : No backups files pending restore"
        if ($ConsoleFeedback -eq "1") 
        {
            Write-Host "-----------------------------------------"
            Write-Host "No new backups found, nothing to restore"
        }   
    }
 
    Write-Host "-----------------------------------------"
    Write-Host "Restore Gene Encountered the Error Below "
    Write-Host "-----------------------------------------"
    Write-Host $Error
    Write-Host "-----------------------------------------"
    Throw "Error in Restore Gene shown above"
}
                     
# Save correctly sequenced restore commands to a hash table
$hash=@{}
foreach ($command in $LogShippingLight_Results)
{
  $hash.Add($command.SortSequence,($command.TSQL))
}
$pendingcmds = $hash.GetEnumerator() | Sort-Object -Property key
          
# Save highest LSN for Restore Log file
foreach ($LSN in $LogShippingLight_Results)
{
  if ($LogShippingLastLSN -lt $LSN.Last_LSN) {$LogShippingLastLSN = $LSN.Last_LSN}
}
              
if ($pendingcmds.Value -eq $null) {
      Write host"-----------------------------------------------------".
      Write-Host "No Backup Files to restore."
      Write host"-----------------------------------------------------".
      Throw "No Backup Files to restore."
}

                     
# ==============================================================================
# Execute the RESTORE commands on the standby server
foreach ($pendingcmd in $pendingcmds)
{
  if ($KillConnections -eq 1) {f_killconnections}
  
  # Removed in V6.57
  #$DBName_nospaces = $DBName -replace(" ","_")                  # doesn't like spaces 
  #$DBName_nospaces = $DBName_nospaces -replace("\.","_")            # doesn't like full stops
  #$DBName_nospaces = $DBName_nospaces -replace("-","_")            # doesn't like hyphens
  #$cmd = ";DECLARE @msg_" + $DBName_nospaces + " VARCHAR(1000) " + $pendingcmd.value
     
                     
  if ($ConsoleFeedback -eq "1") 
  {
    Write-Host "-----------------------------------------"
    Write-Host $pendingcmd.value
  }
                     
  try
  {
    if ($DebugMode -eq "1") 
    {
        $DebugLog = $DebugLog + $pendingcmd.value
    }
    else
    {
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($pendingcmd.value, $SQLConnectionStandby)
        $SQLCommand.CommandTimeout=65535
        $SQLCommand.ExecuteScalar()
    }
  }
  catch
  {
      Write-Host "-----------------------------------------"
      Write-Host "Restore Gene Encountered the Error Below "
      Write-Host "-----------------------------------------"
      Write-Host $Error
      Write-Host "-----------------------------------------"
      Throw "Error in Restore Gene shown above"
  }                    
  sleep -Seconds 1
}
                     
if ($ConsoleFeedback -eq "1") {
  Write-Host "-----------------------------------------"
  Write-Host "RestoreGene Batch Execution Complete"
  Write-Host "-----------------------------------------"
 
}
                     
# ==============================================================================
# Restore Log File final processing, record completion details
$EndTime = Get-Date -Format s
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("EndTime"))
$oXMLSystem.SetAttribute("Description",$EndTime) 
                     
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("LogShippingLastLSN"))
$oXMLSystem.SetAttribute("Description",$LogShippingLastLSN)
              
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("RestoreScript"))
$oXMLSystem.SetAttribute("DebugLog",$DebugLog)
                     
$oXMLDocument.Save($RestoreLog) 

32 replies

  1. Hello,

    First of all thank you very much for this very useful and powerful script, also I’m facing an error that I don’t really understand where it’s coming from here is my setup:

    Windows Server 2012 R2
    Powershell v5.1
    Sql Server 2016 Dev edition
    SSMS v18.2
    Both machines are in the AD Domain,
    I’m using on both machine my AD account which is member of the local Admin group of each server
    The access to Administrative shares is working properly.
    My account is member of the sysadmin Server Roles of both SQL server
    Both server have the “Allow remote connections to this server” checked.
    Both SQL server service are running under my AD account

    So I’m trying to restore a back from server dlt-sin-vm1 to dlt-sin-vm2, and here is the command I’m triggering:

    PS_restoregene.ps1 -RestoreLog ‘C:/Data/autorestore.log’ -PrimaryServer dlt-sin-vm1 -StandbyServer dlt-sin-vm2 -DBName AdventureWorksLT2014 -TargetDBName AdventureWorksLT2014 -ExcludeDiffAndLogBackups ‘1’ -WithReplace ‘1’-WithRecovery ‘1’ -WithMoveDataFiles ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’ -WithMoveLogFile ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’ -WithCHECKDB ‘1’

    And here is the output of the command:

    Name : LogData
    LocalName : LogData
    NamespaceURI :
    Prefix :
    NodeType : Element
    ParentNode : #document
    OwnerDocument : #document
    IsEmpty : True
    Attributes : {}
    HasAttributes : False
    SchemaInfo : System.Xml.XmlName
    InnerXml :
    InnerText :
    NextSibling :
    PreviousSibling :
    Value :
    ChildNodes : {}
    FirstChild :
    LastChild :
    HasChildNodes : False
    IsReadOnly : False
    OuterXml :
    BaseURI :
    PreviousText :

    —————————————–
    RestoreGene Batch Execution Starting
    —————————————–
    EXEC dbo.sp_RestoreGene @Database = ‘AdventureWorksLT2014’, @TargetDatabase = ‘AdventureWorksLT2014’,@WithMoveDataFiles =
    ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’, @WithMoveLogFile = ‘C:\Program Files\Microsoft SQ
    L Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’, @WithMoveFileStreamFile = ”, @FromFileFullUNC = ”, @FromFileDiffUNC = ”, @Fr
    omFileLogUNC= ”, @StopAt = ‘2019-08-20T16:02:04’, @StandbyMode = ‘0’, @WithReplace = ‘1’ , @WithRecovery = ‘1’, @WithCHEC
    KDB = ‘1’, @DropDatabaseAfterRestore = ” , @BlobCredential = ”, @RestoreScriptReplaceThis = ”, @RestoreScriptWithThis =
    ”, @SetSingleUser = ‘1’, @ExcludeDiffAndLogBackups = ‘1’, @IncludeDeviceType7 = ‘1’, @IncludeDeviceType102 = ‘1’, @Inclu
    deDeviceType2 = ‘1’, @IncludeDeviceType9 = ‘1’
    —————————————–
    BEGIN TRY ALTER DATABASE [AdventureWorksLT2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END TRY BEGIN CATCH PRINT’SET SING
    LE USER FAILED’ END CATCH
    —————————————–
    RESTORE DATABASE [AdventureWorksLT2014] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Bac
    kup\2backup.bak’ WITH REPLACE, FILE = 1, NORECOVERY, STATS=10 , MOVE N’AdventureWorksLT2008_Data’ TO ‘C:\Program Files\Mi
    crosoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MSSQL13_AdventureWorksLT2014.mdf’ , MOVE N’AdventureWorksLT2008_Log’ TO
    ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MSSQL13_AdventureWorksLT2014.ldf’
    —————————————–
    Restore Gene Encountered the Error Below
    —————————————–
    Exception calling “ExecuteScalar” with “0” argument(s): “Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\
    MSSQL13.MSSQLSERVER\MSSQL\Backup\2backup.bak’. Operating system error 2(The system cannot find the file specified.).
    RESTORE DATABASE is terminating abnormally.” The specified module ‘SQLASCmdlets’ was not loaded because no valid module fi
    le was found in any module directory. No snap-ins have been registered for Windows PowerShell version 5.
    —————————————–
    Error in Restore Gene shown above
    At C:\Data\SQLScript\PROD\PS_restoregene.ps1:434 char:7
    + Throw “Error in Restore Gene shown above”
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Error in Restore Gene shown above:String) [], RuntimeException
    + FullyQualifiedErrorId : Error in Restore Gene shown above

    PS C:\Data\SQLScript\PROD> Import-Module SqlServer -DisableNameChecking -Verbose
    Import-Module : The specified module ‘SqlServer’ was not loaded because no valid module file was found in any module
    directory.
    At line:1 char:1
    + Import-Module SqlServer -DisableNameChecking -Verbose
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (SqlServer:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    Don’t really understand what is missing, here, should I install SQLASCmdlets but seems to be for the Analysis service and don’t see it used anywhere in the script…
    Also the account have full access to the backup file. And the path to file is correct, when testing it through Explorer.

    Thank you for the help !

    Like

    • Hi Matthieu, on the primary server ‘VM1’, you could trying sharing the backup folder, then in the call to the Restore Gene PowerShell command, pass the share name as parameter -FromFileFullUNC

      Like

      • now that you are saying it, seems obvious…
        Also I have added the -FromFileFullUNC to my previous command and it seems the last slash is stripped out…

        “`
        C:\Data\SQLScript\PROD\PS_restoregene.ps1 -RestoreLog ‘C:/Data/autorestore.log’ -PrimaryServer dlt-sin-vm1 -StandbyServer dlt-sin-vm2 -DBName AdventureWorksLT2014 -TargetDBName AdventureWorksLT2014 -ExcludeDiffAndLogBackups ‘1’ -WithReplace ‘1’-WithRecovery ‘1’ -WithMoveDataFiles ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’ -WithMoveLogFile ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\’ -WithCHECKDB ‘1’ -FromFileFullUNC ’\\dlt-sin-vm1\c$\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\’
        “`

        See the error message:
        RESTORE DATABASE is terminating abnormally.” No snap-ins have been registered for Windows PowerShell version 5. Error in Restore Gene shown above Exception calling “ExecuteScalar” with “0” argument(s): “Cannot open backup device ‘\\dlt-sin-vm1\c$\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup2backup.bak’. Operating system error 2(The system cannot find the file specified.).

        Like

  2. Hi Paul,

    I’m running into an issue where below command is trying to restore log backup from the full back path, even though I mentioned -ExcludeDiffAndLogBackups $null and diff and log backup paths to null (-FromFileDiffUNC $null -FromFileLogUNC $null)

    If I take a fresh full backup and then run above command, it works fine.
    If I take a log backup after a full backup and then try above command then it is looking for .trn file at full back up path given for FromFileFullUNC and fails saying that it cannot find the path.

    Can you please advise what else I need to do to just restore from full backup and not to look for diff or log backup files?

    C:\DBA\ps_RestoreGene.ps1 -RestoreLog ‘C:\DBA\Alpha.txt’ -PrimaryServer QASQL1 -StandbyServer QASQL2 -DBName Alpha -TargetDBName Alpha1 -FromFileFullUNC ‘\\QASQL1\Backup\qaclus$AG_QA\Alpha\FULL\’ -ExcludeDiffAndLogBackups $null -StopAt $null -WithRecovery 1 -WithReplace 1

    Like

  3. Is there a s parameter available to restore all user databases from Primary instance to Standby Instance?

    Like

  4. I have found an issue with the check and kill of connections.

    line 148: $activeconnections = “SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID(‘” + $DBName + “‘)”
    incorrectly uses $DBName when executing against the StandbyServer. It should be using $TargetDBName instead.

    line 332: if ($KillConnections -eq 1) {f_killconnections -DBName $DBName}
    incorrectly provides $DBName to f_killconnections which is also executing against the StandbyServer. It should be using $TargetDBName instead.

    This will only cause issue when the restore database name is different then the source db name.

    If i have made an error please straighten me out.

    Thanks again for this great implementation.

    Like

  5. Apologies, sent to wrong address yesterday. corrected it now, please let me know if you have received my email with a screenshot.

    Like

  6. Hi Kiran,
    I didn’t receive an email but have tried the PoSh call again and still can’t reproduce the problem, all WITH MOVE parameters were applied to the restore script as expected. Are you using the latest versions of the T-SQL and PoSh scripts.
    Paul

    Like

  7. Thanks for responding to this Paul, I’m noticing that the ps call is trying to restore to the database’s original locations ,
    i.e., if the MDF file of your database is in D drive and LDF on L drive then the restore script is generated to move the MDF to D drive and LDF to L drive on the secondary server as well even if the -WithMoveDataFiles is pointing to “G” drive and Ldf to “H” drive.

    Below is the PS call that worked for me with the previous version (pls exclude filestream call, this is new included from the latest version).

    C:\RestoreGene_Sales_DBSERVER\ps_RestoreGene.ps1 `
    -RestoreLog “C:\RestoreGene_Sales_DBSERVER\LogToAutoSync_Sales_DBSERVER.xml” `
    -PrimaryServer “DBSERVER” `
    -StandbyServer “PER3SVR31\SECONDINSTANCE” `
    -DBName “SalesDatabase” `
    -TargetDBName “SalesDatabase” `
    -WithReplace “1” `
    -WithMoveDataFiles “G:\SecondInstance\” `
    -WithMoveFileStreamFile “F:\SecondInstance\FileStreamFiles\” `
    -WithMoveLogFile “H:\SecondInstance\” `
    -FromFileFullUNC “\\BackupServer\sqlbackups$\DBSERVER\SalesDatabase\FULL\” `
    -FromFileDiffUNC “\\BackupServer\sqlbackups$\DBSERVER\SalesDatabase\DIFF\” `
    -FromFileLogUNC “\\BackupServer\sqlbackups$\DBSERVER\SalesDatabase\LOG\” `
    -StopAt $null `
    -StandBy “1” `
    -WithCHECKDB “0” `
    -LogShippingInitialize “0” `
    -Log_Reference “SalesInitializeLogShippingTest” `
    -KillConnections “1” `
    -ConsoleFeedback “1” `
    -DebugMode “0”`
    -Verbose

    Now this call just ignores the parameters passed to -WithMoveDataFiles “G:\SecondInstance\” ` ,-WithMoveLogFile “H:\SecondInstance\” `
    and generates a restore command to restore the database on secondary server to D and L drives , in my case i don’t have D and L drives on Secondary server, this is how i could find this issue.

    Please can you try and replicate this scenario.

    Regards,
    Kiran

    Like

    • Hi Kiran,
      Could you email a sanitized copy of the ps_RestoreGene console output to paulbrewer@yahoo.co.uk , I’ve tried to replicate the problem behaviour but it works as expected in my test lab.
      Thanks
      Paul

      Like

      • HI Paul,Thanks for the response , it makes more sense to communicate this issue via email rather than on the Blog.I have attached the output for your opinion , please note that the E drive and L drive are the original locations for mdf and ldf’s of the database , where as I’m passing F: and E: to move the mdf and ldf’s. Appreciate your help. Regards,Kiran

        Date: Tue, 19 Jul 2016 09:11:07 +0000 To: jgkiran@live.com

        Like

  8. Hi Kiran,
    The fix to unsure standby file names are unique (so multiple instances of ps_RestoreGene can restore the same set of backup files) introduced a bug where the standby filename was invalid, that’s been corrected in the latest version of the sp_RestoreGene.

    I ran this test ps_RestoreGene Call:

    Set-Location -Path “C:\RestoreGene”

    .\ps_RestoreGene.ps1 `
    -RestoreLog “C:\RestoreGene\log.xml” `
    -PrimaryServer “PB31” `
    -StandbyServer “PB33” `
    -DBName “Workspace” `
    -TargetDBName “workspace_restore” `
    -WithReplace “1” `
    -WithMoveDataFiles “c:\temp\” `
    -WithMoveLogFile “c:\temp\” `
    -WithMoveFileStreamFile “c:\temp\” `
    -FromFileFullUNC ‘\\pb31\share\Backups\PB31\workspace\FULL\’ `
    -FromFileDiffUNC ‘\\pb31\share\Backups\PB31\workspace\DIFF\’ `
    -FromFileLogUNC ‘\\pb31\share\Backups\PB31\workspace\LOG\’ `
    -StopAt $null `
    -StandBy “1” `
    -WithRecovery “1” `
    -WithCHECKDB “1” `
    -LogShippingInitialize “1” `
    -Log_Reference “Posh Driver Test” `
    -KillConnections “1” `
    -ConsoleFeedback “1” `
    -BlobCredential “Paul” `
    -RestoreScriptReplaceThis $null `
    -RestoreScriptWithThis $null `
    -DebugMode “0”

    It performed the following which were the desired actions:

    —————————————–
    RestoreGene Batch Execution Starting
    —————————————–
    EXEC dbo.sp_RestoreGene @LogShippingVariableDeclare = 0, @Database = ‘Workspace’,@L
    og_Reference = ‘Posh Driver Test’, @TargetDatabase = ‘workspace_restore’,@WithMoveD
    ataFiles = ‘c:\temp\’, @WithMoveLogFile = ‘c:\temp\’, @WithMoveFileStreamFile = ‘c:
    \temp\’, @FromFileFullUNC = ‘\\pb31\share\Backups\PB31\workspace\FULL\’, @FromFileD
    iffUNC = ‘\\pb31\share\Backups\PB31\workspace\DIFF\’, @FromFileLogUNC= ‘\\pb31\shar
    e\Backups\PB31\workspace\LOG\’, @StopAt = ‘2016-07-18T18:50:44’, @StandbyMode = ‘1’
    , @WithReplace = ‘1’ , @WithRecovery = ‘1’, @WithCHECKDB = ‘1’ , @BlobCredential =
    ‘Paul’, @RestoreScriptReplaceThis = ”, @RestoreScriptWithThis = ”
    —————————————–
    ;DECLARE @msg_Workspace VARCHAR(1000) ;RESTORE DATABASE [workspace_restore] FROM D
    ISK = N’\\pb31\share\Backups\PB31\workspace\FULL\PB31_workspace_FULL_20160718_15261
    1.bak’ WITH REPLACE, FILE = 1,CHECKSUM, STANDBY =N’\\pb31\share\Backups\PB31\works
    pace\FULL\\workspace1850451800000_ROLLBACK_UNDO.bak ‘, STATS=10, MOVE N’workspace_d
    ata’ TO ‘c:\temp\workspace_data.mdf.workspace_restore_RestoreGene_TargetDBRenamed’,
    MOVE N’workspace_log’ TO ‘c:\temp\workspace.ldf.workspace_restore_RestoreGene_Targ
    etDBRenamed’, MOVE N’workspace1 ‘ TO N’c:\temp\workspace_f1.ndf.workspace_restor
    e_RestoreGene_TargetDBRenamed’, MOVE N’workspace2 ‘ TO N’c:\temp\workspace_f2.nd
    f.workspace_restore_RestoreGene_TargetDBRenamed’, MOVE N’workspace3 ‘ TO N’c:\te
    mp\workspace_f3.ndf.workspace_restore_RestoreGene_TargetDBRenamed’, MOVE N’workspac
    e4 ‘ TO N’c:\temp\workspace_f4.ndf.workspace_restore_RestoreGene_TargetDBRenamed
    ‘, MOVE N’workspace5 ‘ TO N’c:\temp\workspace_f5.ndf.workspace_restore_RestoreGe
    ne_TargetDBRenamed’, MOVE N’workspace6 ‘ TO N’c:\temp\workspace_f6.ndf.workspace
    _restore_RestoreGene_TargetDBRenamed’
    —————————————–
    ;DECLARE @msg_Workspace VARCHAR(1000) ;RESTORE DATABASE [workspace_restore] FROM D
    ISK = N’\\pb31\share\Backups\PB31\workspace\DIFF\PB31_workspace_DIFF_20160718_18055
    1.bak’ WITH FILE = 1,CHECKSUM, STANDBY =N’\\pb31\share\Backups\PB31\workspace\FULL
    \\workspace1850451800000_ROLLBACK_UNDO.bak ‘, STATS=10, MOVE N’workspace_data’ TO ‘
    c:\temp\workspace_data.mdf.workspace_restore_RestoreGene_TargetDBRenamed’, MOVE N’w
    orkspace_log’ TO ‘c:\temp\workspace.ldf.workspace_restore_RestoreGene_TargetDBRenam
    ed’
    —————————————–
    ;DECLARE @msg_Workspace VARCHAR(1000) ;RESTORE LOG [workspace_restore] FROM DISK =
    N’\\pb31\share\Backups\PB31\workspace\LOG\PB31_workspace_LOG_20160718_180745.trn’
    WITH STANDBY =N’\\pb31\share\Backups\PB31\workspace\FULL\\workspace1850451800000_RO
    LLBACK_UNDO.bak ‘, CHECKSUM, FILE = 1 ,STOPAT = ‘2016-07-18 18:50:44′ ,MOVE N’work
    space_data’ TO ‘c:\temp\workspace_data.mdf.workspace_restore_RestoreGene_TargetDBRe
    named’ ,MOVE N’workspace_log’ TO ‘c:\temp\workspace.ldf.workspace_restore_RestoreG
    ene_TargetDBRenamed’
    —————————————–
    ;DECLARE @msg_Workspace VARCHAR(1000) ;SET @msg_workspace = ‘Posh Driver Test WITH
    RECOVERY ‘; RAISERROR (@msg_workspace,0,0) WITH LOG;RESTORE DATABASE [workspace_re
    store] WITH RECOVERY
    —————————————–
    ;DECLARE @msg_Workspace VARCHAR(1000) ;SET @msg_workspace = ‘Posh Driver Test CHECK
    DB ‘; RAISERROR (@msg_workspace,0,0) WITH LOG;DBCC CHECKDB(‘workspace_restore’) WI
    TH NO_INFOMSGS, ALL_ERRORMSGS
    —————————————–
    RestoreGene Batch Execution Complete

    Thanks
    Paul

    Like

  9. Hi Paul,

    Thanks for this fix , just tested it and works like a charm.

    Not sure if i’m doing anything wrong, but i’m not 🙂 ..after this fix seems like the parameters -WithMoveDataFiles , -WithMoveLogFile are not working , the restore command is picking up the original database locations for data and log files on stand by server with -LogShippingInitialize “1” even after supplying -TargetDBName and -WithReplace parameters , can you please check this.

    Appreciate your help to the SQL community.

    Regards,
    Kiran

    Like

  10. HI Paul,

    Thanks for the response

    1.Yes , as mentioned above each instance is using an entire separate set of Log file and sp_restoreGene, Ps_restoreGene files .

    2. Haven’t tried with StandBy “0” as in my case i need to initiate replication .

    Regards,
    Kiran

    Like

    • Hi Kiran,
      Please download the latest version of sp_RestoreGene (V5.5) and try that, it contains a fix for your problem.

      https://paulbrewer.wordpress.com/sp_restoregene/

      The problem was 2 concurrent restores would try and generate the same rollback undo file in the same folder, the modification in V5.5 adds a unique ID to the rollback undo file names to avoid the clash.

      Thanks very much for identifying the limitation.
      Best wishes
      Paul

      Like

  11. Hi Kiran,
    thats another limitation you’ve found. The 2 instances on ps_restoregene will try and create the same rollback file in the same backup folder. I’ll try and find a solution over the weekend.

    Regards
    Paul

    Like

  12. HI Paul,

    I’m trying to use a single source of backup location (Full,Diff,Trn) of a database for restoring it on two separate servers at different schedules with separate instances of ps_restoreGene, this resulted in below error even though I renamed the _Undo.bak to _undo2.bak in sp_restoregene on the second server , i assumed the second server will use DatabaseName_ROLLBACK_UNDO2.bak file as its undo file, but i was wrong. Can you please advise how this can be achieved.

    Here is the error :
    DatabaseName_ROLLBACK_UNDO.bak’ is not a valid undo file for database ‘Database (databaseName ID 5).
    Verify the file path, and specify the correct file.

    Thanks,
    Kiran

    Like

  13. Version 5.4 Includes a new parameter to move file stream files to the folder name supplied as a parameter.
    Thanks for the suggestions
    Paul

    Like

  14. Version 5.1 released today includes the following changes:

    1. If Target database name is supplied (when restoring to a database name different to the back up), a string is appended to the end of the filenames in the format : c:\sql\data\name.mdf. @TargetDatabase_ + _RestoreGene_TargetDBRenamed. This makes the filenames unique so the restore can be on the same instance using the same folders.

    2. If a backup was taken to Azure url storage, the restore script constructed includes ‘FROM URL’ rather than ‘FROM FILE’. The credentials used to take the backup can be supplied as a new parameter.

    3. Two new parameters – one for a string scan and one for replace that can optionally be applied to the restore script.

    4. The PowerShell driver script now defaults ‘With Standby’ to false.

    Thanks for the feedback, all of these changes were suggested recently.
    Paul

    Like

  15. Version 4 released which includes the following fixes:
    – Lots of unnecessary _msg variables removed
    – Support for a single backup fork added
    – Support for default backup paths, for UNC backups there is now no need to declare them in ps_RestoreGene
    – Support for databases which a – in their name

    All feedback and suggestions most welcome, thanks
    Paul

    Like

  16. There is an issue if the database name contains a hyphen character.
    changed the script by adding the last line to the section below to resolve. Also had to add an additional REPLACE() throughout the stored procedure.

    # $cmd = “;DECLARE @msg_” + $DBName + ” VARCHAR(1000) ” + $pendingcmd.value
    $DBName_nospaces = $DBName -replace(” “,”_”) # doesn’t like spaces
    $DBName_nospaces = $DBName_nospaces -replace(“\.”,”_”) # doesn’t like full stops
    $DBName_nospaces = $DBName_nospaces -replace(“-“,”_”) # doesn’t like hyphens

    Like

  17. Version 3.6 includes 2 fixes both identified and solved by a colleague. Query timeouts during big restores and a problem when database names contained a space or full stop. It also includes a change so script exits rather than errors if there are no new log backup files pending restore, in log shipping mode.

    Like

  18. Version 3.5, just released, includes a new PoSh parameter ‘ConsoleFeedback’. It defaults to 1 and sends write host status updates but can be changed to “0” for SQL Agent Job Steps. Thanks to geostock for a great suggestion.

    Like

  19. Running it via a SQL Server Agent job requires that the “Write-Host” be done away with.

    I’m executing it dynamically because I have over 500 databases to choose from, and I pick the one that hasn’t been restored as recently as the others. So I have a PowerShell command invocation that executes a stored procedure, and gets the values from the stored procedure to populate the parameters needed for your PS_restoregene script.

    To get it to work, I had to override write-host. Here’s the Job text:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=MyDatabaseStandByServer;Database=master;Integrated Security=True”
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand(“sp_GetNextBackupToRestore”, $SqlConnection)
    $SqlCmd.CommandType = [System.Data.CommandType]’StoredProcedure’
    $SqlCmd.Parameters.Add(“@powershellOutput”, “”) | Out-Null
    $SqlCmd.Parameters[“@powershellOutput”].Direction = [System.Data.ParameterDirection]::Output
    $SqlCmd.ExecuteNonQuery() | Out-Null
    $psCommand = $SqlCmd.Parameters[“@powershellOutput”].Value
    Invoke-Expression $psCommand

    $psCommand is a dynamically constructed powershell command string, constructed using results from the stored procedure:

    $command = “D:\Scripts\ps_RestoreGene.ps1 -RestoreLog D:\Scripts\Log1.xml -PrimaryServer SQL1 -StandbyServer SQLTE1 -DBName SampleDB -FromFileFullUNC ‘\\SQL1\D`$\Backup\’ -TargetDB SampleDB_TEST_RESTORE_20140819 -WithMoveDataFiles D:\Data\ -WithMoveLogFile D:\Log -WithRecovery 1 -WithCheckDB 1”

    In the ps_restoreGene.ps1, I added the following line after the parameters, but before everything else so that I could invoke it from the SQL Agent:

    function write-host {}

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: