## Stop SqlServer Manager ###

Stop-Process -Name ssms -Force

##### Delete File ##########

$limit = (Get-Date).AddMinutes(-60)
$path="C:ProgramFilesMicrosoftSQLServerMSSQL14.MSSQLSERVERMSSQLBackup"
$Extension = "*."

# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force

##### File Download ##########

$localPath = "C:Program FilesMicrosoft SQLServerMSSQL14.MSSQLSERVERMSSQLBackup"
$remotePath = "/production/"

try
{
# Load WinSCP .NET assembly
Add-Type -Path "C:Program Files (x86)WinSCPWinSCPnet.dll"

# Set up session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = "sftpserver@hosting.com"
    UserName = "redacted"
    Password = "redacted"
    SshHostKeyFingerprint = "redacted"
}

$session = New-Object WinSCP.Session

    $session = New-Object WinSCP.Session

    try
    {
        # Connect
        $session.Open($sessionOptions)

        # Get list of files in the directory
        $directoryInfo = $session.ListDirectory($remotePath)

        # Select the most recent file
        $latest =
            $directoryInfo.Files |
            Where-Object { -Not $_.IsDirectory } |
            Sort-Object LastWriteTime -Descending |
            Select-Object -First 1

        # Any file at all?
        if ($latest -eq $Null)
        {
            Write-Host "No file found"
            exit 1
        }

        # Download the selected file
        $session.GetFiles(
            [WinSCP.RemotePath]::EscapeFileMask($latest.FullName), $localPath).Check()
    }
    finally
    {
        # Disconnect, clean up
        $session.Dispose()
    }

    exit 0
}
catch
{
    Write-Host "Error: $($_.Exception.Message)"
    exit 1
}

##### Restore Database ######

$FileName = Get-ChildItem -path $path


Restore-SqlDatabase -ServerInstance "MACHINEINSTANCE" -Database "DB_Prod" -BackupFile $FileName -ReplaceDatabase