Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Do backup of SQL database and ftp / ftps it to remote location

leave a comment »

Here is the problem : you have SQL server with some production databases and you need to secure backups of those databases in some remote location. In our case this remote location is accessible only thru FTPS.

To do that you have at least two option:

1. You can go and use SqlBackupAndFTP application http://sqlbackupandftp.com/ . In case of that option you can use that tool for free to do backup and ftp of max 2 databases and you are limited to FTP. If you are force to use FTPS like we are, then you need to either buy full version of find some other way.

2. You can build peace of PowerShell script and run it as part of database maintenance plan. And that is the approach I went for.

Below you can find peace of PowerShell script I wrote to achieve that task:

[Net.ServicePointManager]::ServerCertificateValidationCallback={$true}
$Dir = "c:\temp"
foreach($item in (dir $dir))
{
    $content = [System.IO.File]::ReadAllBytes($item.FullName)
    $ftp = [System.Net.FtpWebRequest]::Create("ftp://{FtpServer}/{HomeFolder}/"+$item.Name)
    $ftp = [System.Net.FtpWebRequest]$ftp
    $ftp.UsePassive = $true
    $ftp.UseBinary = $true
    $ftp.EnableSsl = $true
    $ftp.Credentials = new-object System.Net.NetworkCredential("{UserName}","{Password}")
    $ftp.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile
    $rs = $ftp.GetRequestStream()
    $rs.Write($content,0,$content.Length)
    $rs.Close()
    $item.Delete();
}

First line of the scripts resolve problem with missing SSL certificate on your SQL server. Without that you will not be able to use above script as system will not trust remote FTP server (unless FTP SSL certificate is in your trusted store)

Next we iterate thru each file in folder pointed in line two and for each of it we open FTPS ($ftp.EnableSsl = $true) connection and upload our file.

Please note that above approach has one disadvantage : to send file, we load it into memory. If you will have bigger files then this can be a problem. Anyway I’ll leave to solve that problem to you (if you know .net then it shouldn’t be a big deal)

happy PowerShelling.

Advertisements

Written by Mariusz Gorzoch

15 October 2013 at 22:03

Posted in Powershell, SQL

Tagged with ,

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: