$FtpTargetDirectory = "/Database/Backup"
$HostName = "dtbunnsql.cloudapp.net"
$UserName = "dtbunnsql"
$Password = "Passw0rd"
$BackupDirectory = "D:\tmp\bunnigbak"

Add-Type -Path "WinSCPnet.dll"


 $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = $HostName
        UserName = $UserName
        Password = $Password
        SshHostKeyFingerprint = "ssh-rsa 1024 70:34:fd:93:18:e2:25:df:ee:82:1d:d2:a7:71:11:b5"
    }


function GetLatestFile()
{
    #   $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    #     Protocol = [WinSCP.Protocol]::Sftp
    #     HostName = $HostName
    #     UserName = $UserName
    #     Password = $Password
    #     SshHostKeyFingerprint = "ssh-rsa 1024 70:34:fd:93:18:e2:25:df:ee:82:1d:d2:a7:71:11:b5"
    # }

    $fileDate = 0
    $session = New-Object WinSCP.Session
    $targetDate = 20100101
    $filesToGet = New-Object System.Collections.ArrayList

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

        # List directory
        $directory = $session.ListDirectory($FtpTargetDirectory)

        # Get latest file
        foreach ($fileInfo in $directory.Files)
        {
            Write-Host "Writting out the name $fileInfo.Name"

            $fName = $fileInfo.Name

            if ($fName.length -gt 8)
            {
                $valfile = $fileInfo.Name.Substring($fileInfo.Name.length - 12, 8)
                $valOk = [int32]::TryParse($valfile , [ref] $fileDate)
                if ($valOk -eq $True)
                {
                    if ($targetDate -lt $fileDate)
                    {
                    $targetDate = $fileDate
                    }
                }
            }
        }
    }
    finally
    {
        # Disconnect, clean up
        $session.Dispose()
    }

    Write-Host $targetDate
    return $targetDate
}

function CheckIfBackupPatternExist($pattern)
{
    $path = "$BackupDirectory\*$pattern*"
    return test-path $path -pathtype leaf
}

function GetFile($targetFile) {

#    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{

#         Protocol = [WinSCP.Protocol]::Sftp
#         HostName = $HostName
#         UserName = $UserName
#         Password = $Password
#         SshHostKeyFingerprint = "ssh-rsa 1024 70:34:fd:93:18:e2:25:df:ee:82:1d:d2:a7:71:11:b5"
#     }

    $sessionOptions.AddRawSettings("FSProtocol", "2")
    $session = New-Object WinSCP.Session

    try
    {
        # Connect
        $session.Open($sessionOptions)
        #$session.GetFiles("/Database/Backup/" + $targetFile, "d:\tmp\bunnigbak\*")

        $targetFile = "20181011"

        $ftpPattern = "/Database/Backup/*$targetFile*.BAK";
        $ftpTargetDirectory = $BackupDirectory + "\*"

        Write-Host("Ftp pattern to download: $ftpPattern Target folder: $ftpTargetDirectory")
        $session.GetFiles($ftpPattern, $ftpTargetDirectory)

        # Your code
    }
    finally
    {
        $session.Dispose()
    }
}

function Main()
{
    $filePattern = GetLatestFile
    Write-Host "Latest backup obtained from ftp site [$filePattern]"

    $backupFilesExist = CheckIfBackupPatternExist($filePattern)

    if ($backupFilesExist -eq $false)
    {
        Write-Host "Downloading latest backup from server......please wait"
        GetFile $filePattern
    }
    else {
        Write-Host "Backup files already exists. No restore will happened."
    }
}

##### Execute Main function #######
Main