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 
                    
---------------------------------------------------------------------------#>
#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}
}
             
                    
# ==============================================================================
# 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) 

21 replies

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  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 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

  12. 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

  13. 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

  14. 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

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

    Like

  16. 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: