# ======================================================================================================== 
# PARAMETERS AND DEFAULTS  
# ======================================================================================================== 
Param($DBName `
    ,$ToFileFolder `
    ,$ToLogFolder `
    ,$FullBackupUNC `
    ,$DiffBackupUNC `
    ,$LogBackupUNC `
    ,$StopAt `
    ,$StandBy `
    ,$WithRecovery `
    ,$PrimaryServer `
    ,$StandbyServer `
    ,$RestoreLog `
    ,$Initialize `
    ,$KillConnections `
    )
    
# DEFAULTS    
if ($DiffBackupUNC -eq $null) {$DiffBackupUNC = $FullBackupUNC} # UNC path to backups for Standby Server to access primary DIFF backups
if ($LogBackupUNC -eq $null) {$LogBackupUNC = $FullBackupUNC} # UNC path to backups for Standby Server to access primary LOG backups
#$StopAt = '2013-06-09T20:53:03'
if ($StopAt -eq $null) {$StopAt = Get-Date -Format s}
if ($StandBy -eq $null){$StandBy = 1} # 0 for NORECOVERY
if ($WithRecovery -eq $null){$WithRecovery = 0} # 1 for final WITH RECOVERY restore and DBCC CHECKDB, IE, to bring Standby online
#PoSh VARIABLES
if ($Initialize -eq $null) {$Initialize = 1} # 0 for incremental diff and logs only
if ($KillConnections -eq $null) {$KillConnections = 1} # 0 cancel restore if connections are blocking
if ($RestoreLog -eq $null) {$RestoreLog = $FullBackupUNC + "LogShippingLight_Log.csv"}

# REPORT RUNTIME VALUES
">>>>> RUNTIME PARAMETERS" | Out-Default
"DBName  = " + $DBName | Out-Default
"ToFileFolder = " + $ToFileFolder | Out-Default
"ToLogFolder = " + $ToLogFolder | Out-Default
"FullBackupUNC = " + $FullBackupUNC | Out-Default
"DiffBackupUNC = " + $DiffBackupUNC | Out-Default
"LogBackupUNC = " + $LogBackupUNC  | Out-Default
"StopAt = " + $StopAt  | Out-Default
"StandBy = " + $StandBy  | Out-Default
"WithRecovery = " + $WithRecovery  | Out-Default
"PrimaryServer = " + $PrimaryServer  | Out-Default
"StandbyServer = " + $StandbyServer  | Out-Default
"RestoreLog = " + $RestoreLog  | Out-Default
"Initialize = " + $Initialize  | Out-Default
"KillConnections = " + $KillConnections  | Out-Default
"<<<<<< RUNTIME PARAMETERS" | Out-Default
" " | Out-Default
    


# ======================================================================================================== 
# INITIALIZE AND VALIDATE PARAMETERS  
# ======================================================================================================== 
#Snapin for the Invoke-SQLCmd cmdlet
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Import-Module SQLPS -DisableNameChecking
Set-Location -Path Alias:
#
if ($PrimaryServer -eq $null -or $StandbyServer -eq $null -or $DBName -eq $null)
{
    throw "BOTH PRIMARY AND STANDBY SERVER NAMES ARE REQUIRED PARAMETERS AS IS DATABASE NAME"
}

$primarytest = New-Object Microsoft.SQLServer.Management.Smo.Server($PrimaryServer)
if ($primarytest.InstanceName -eq $null)
{
    throw "PRIMARY SERVER INSTANCE NOT FOUND"
}    

$standbytest = New-Object Microsoft.SQLServer.Management.Smo.Server($StandbyServer)
if ($standbytest.InstanceName -eq $null)
{
    throw "STANDBY SERVER INSTANCE " + $PrimaryServer + "NOT FOUND"
}    

if ($KillConnections -eq 0)
{

    $activeconnections = "SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('" + $DBName + "')"
    $check = Invoke-Sqlcmd -Query $activeconnections -Database "master" -ServerInstance $StandbyServer
    if ($check.spid -ne $null)
    { 
        throw "ACTIVE CONNECTIONS ARE BLOCKING RESTORE COMMANDS ON THE STANDBY SERVER"
    }
}

if ($FullBackupUNC -eq $null)
{
    throw "UNC PATH TO PRIMARY BACKUP FOLDER IS REQUIRED"
}    


# ======================================================================================================== 
# GENERATES T-SQL RESTORE COMMANDS DESERIALIZING SECONDARY FILES  
# ======================================================================================================== 
# With Overrides of data log file locations during restore
if ($ToFileFolder -ne $null -and $ToLogFolder -ne $null)
{
    #$LogShippingLight_EXEC = `
    "EXEC dbo.sp_LogShippingLight `
    @Database = '" + $DBName + "', `
    @ToFileFolder = '" + $ToFileFolder + "', `
    @ToLogFolder = '" + $ToLogFolder + "', `
    @FullBackupUNC = '" + $FullBackupUNC + "', `
    @DiffBackupUNC = '" + $DiffBackupUNC + "', `
    @LogBackupUNC = '" + $LogBackupUNC + "', `
    @StopAt = '" + $StopAt + "', ` 
    @StandbyMode = '" + $Standby +"', `
    @WithRecovery = 0"   
}
else
{
    # Without Overrides of data log file locations during restore
    $LogShippingLight_EXEC = `
    "EXEC dbo.sp_LogShippingLight `
    @Database = '" + $DBName + "', `
    @FullBackupUNC = '" + $FullBackupUNC + "', `
    @DiffBackupUNC = '" + $DiffBackupUNC + "', `
    @LogBackupUNC = '" + $LogBackupUNC + "', `
    @StopAt = '" + $StopAt + "', ` 
    @StandbyMode = '" + $Standby +"', `
    @WithRecovery = '" + $WithRecovery +"'"   
}

# Converts a command over 2 lines such as this
#    RESTORE DATABASE xx
#    WITH MOVE fg
# Converts (PIVOT's/Deserializes) it to a single line like this
#    RESTORE DATABASE xx WITH MOVE fg
# Places result into hash table $pendingcmds

$LogShippingLight_Results = Invoke-SQLCmd -Query $LogShippingLight_EXEC -QueryTimeout 6000 -Database "msdb" -ServerInstance $PrimaryServer
if ($LogShippingLight_Results -ne $null)
{

    foreach ($ndffile in $LogShippingLight_Results)
    {
        if ($ndffile.BackupDevice -eq "SECONDARY FULL")
        {
            if ($ndffiles -notcontains $ndffile.TSQL) {$ndffiles = $ndffiles + $ndffile.TSQL}
        }
    }
    
    #$ndffiles | Out-Default
    
    $LogShippingLight_Results = $LogShippingLight_Results | Where-Object {$_.BackupDevice -ne "SECONDARY FULL" -and $_.BackupDevice -ne "SECONDARY DIFF"}    
    $hash=@{}
    foreach ($command in $LogShippingLight_Results)
    {
        if ({$command.TSQL -contains "*RESTORE*"} -and {$command.TSQL -notcontains "*WITH RECOVERY*"}) {$hash.Add($command.BackupDate,($command.TSQL + $ndffiles))}
        else {$hash.Add($command.BackupDate,$command.TSQL)}
    }
    $pendingcmds = $hash.GetEnumerator() | Sort-Object -Property key
}
else #database has no backups
{
    throw "NO BACKUP FILES FOUND FOR INSTANCE " + $PrimaryServer + ", DATABASE - " + $DBNAME + ", CANNOT RESTORE TO INSTANCE " +  $StandbyServer
}

# ======================================================================================================== 
#  FUNCTION TO KILL BLOCKING CONNECTIONS  
# ======================================================================================================== 
function f_killconnections($DBName)
{
    $KillQueryConstructor = "SELECT ';KILL ' + CAST(spid AS VARCHAR(4)) + '' FROM sys.sysprocesses WHERE dbid = DB_ID('" + $DBName + "')"
    $KillCommands = Invoke-Sqlcmd -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
        }    
    }
}


# ======================================================================================================== 
# IF INITIALIZE = YES RESTORE LAST FULL, MOST RECENT DIFFERENTIAL AND LOG BACKUPS 
# ======================================================================================================== 
If ($Initialize -eq 1) 
{
    $hash1=@{}
    foreach ($pendingcmd in $pendingcmds)
    {
        "QUERY IN PROGESS - " + $pendingcmd.value | Out-Default
        # Run RESTORE
        if ($KillConnections -eq 1) {f_killconnections -DBName $DBName}
        try {Invoke-SQLCmd -QueryTimeout 6000 -Query $pendingcmd.value -Database "master" -ServerInstance $StandbyServer}
        catch 
        {
            throw "ERROR RUNNING QUERY "
        }        
        " -------------------------------------------------------------------------------"  | Out-Default
                
        # hash table log for csv
        sleep -Seconds 1
        $hash1.Add($Pendingcmd.name,$pendingcmd.value)          
    } 
    
    # Log restore commands to csv
    If (Test-Path $RestoreLog) {Remove-Item $RestoreLog}            
    foreach ($Restore in ($hash1.GetEnumerator() | Sort-Object Name))
    {
        # Definiton of a new 'Log Record' object
        $RestoreLogObject = @{
        BackupDateTime = $Restore.Name
        RestoreCommand = $Restore.value
        RestoreStopAt = $StopAt}
  
        If (Test-Path $RestoreLog)
        {
            $JustData = New-Object PSObject -Property $RestoreLogObject | ConvertTo-Csv -NoTypeInformation
            $JustData[1] | Add-Content -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8 
        }
        else {New-Object PSObject -Property $RestoreLogObject | Export-Csv -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8 -NoTypeInformation}
    }  
}


# ======================================================================================================== 
# IF INITIALIZE = NO RESTORE RECENT DIFFERENTIAL AND LOG BACKUPS   
# ======================================================================================================== 
If ($Initialize -eq 0) 
{
    $RestoreCmdsAlreadyRun = Import-Csv -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog

    foreach ($RestoreCmdAlreadyRun in ($RestoreCmdsAlreadyRun | Sort-Object BackupDateTime))
    {
        # Definiton of a existing 'Log Record' object
        $RestoreLogObjectExisting = @{
        BackupDateTime = $RestoreCmdAlreadyRun.BackupDateTime
        RestoreCommand = $RestoreCmdAlreadyRun.RestoreCommand
        RestoreStopAt = $RestoreCmdAlreadyRun.RestoreStopAt
        StopAt = $StopAt}   
  
        
        $old = New-Object PSObject -Property $RestoreLogObjectExisting 
        # Check previous STOPAT is less than value supplied
        if ($old.RestoreStopAt -gt $old.StopAt)
        {
            throw "STOPAT PARAMETER IS EARLIER THAN A PREVIOUS RUN " | Out-Default
        }

        # Check if WITH RECOVERY has already been run
        if ($old.RestoreCommand.Contains("WITH RECOVERY"))
        {   
            throw "WITH RECOVERY HAS ALREADY BEEN RUN " | Out-Default
            break
        }
    }
    # Object $old now contains values from the last backup run


    # Run outstanding restore commands
    $hash2=@{}
    foreach ($pendingcmd in $pendingcmds)
    {
        $FULL = "*RESTORING FULL*" 
        if ($pendingcmd.name -ge $old.BackupDateTime -and $pendingcmd.value -notlike $FULL)
        {  
            "QUERY IN PROGESS - " + $pendingcmd.value | Out-Default
            # Run RESTORE
            if ($KillConnections -eq 1) {f_killconnections -DBName $DBName}
            try {Invoke-SQLCmd -QueryTimeout 6000 -Query $pendingcmd.value -Database "master" -ServerInstance $StandbyServer}
            catch 
            {
                throw "ERROR RUNNING QUERY "
                $continue = 0
            }  
            " -------------------------------------------------------------------------------"  | Out-Default
            
            # hash table log for csv
            sleep -Seconds 1
            $hash2.Add($Pendingcmd.name,$pendingcmd.value)          
        }
    }     
               
    # Append new log restore commands to existing csv
    foreach ($Restore in ($hash2.GetEnumerator() | Sort-Object Name))
    {
        # Definiton of a new 'Log Record' object
        $RestoreLogObject = @{
        BackupDateTime = $Restore.Name
        RestoreCommand = $Restore.value
        RestoreStopAt = $StopAt}
        $JustData = New-Object PSObject -Property $RestoreLogObject | ConvertTo-Csv -NoTypeInformation
        $JustData[1] | Add-Content -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8 
    }        
}