Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Archive for the ‘SQL’ Category

SQL backup from PowerShell

with one comment

Today I came against a problem of ‘how to perform backup of database from PowerShell’ script. If you start goggling around it then you will find that there are some extensions with are part of SQL 2012 or feature packs with you can add to your installation… but guess what : I just want to do simple backup without putting too much effort and by the way my server is running SQL Express 2008 R2. Lucky this can be resolved be good old ADO. Please find script to do that below:

# Create and open a database connection
$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=.;database=db;Integrated Security=sspi"
$sqlConnection.Open()
#Create a command object
$tStamp = Get-Date -format yyyy_MM_dd_HHmmss
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "backup database db to disk = ‘c:\backupFolder\db_"+$tStamp+".bak’ with copy_only"
#Execute the Command
$sqlReader = $sqlCommand.ExecuteScalar()
# Close the database connection
$sqlConnection.Close()

Done it and couldn’t be easier

Advertisements

Written by Mariusz Gorzoch

9 January 2014 at 12:23

Posted in Powershell, SQL

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.

Written by Mariusz Gorzoch

15 October 2013 at 22:03

Posted in Powershell, SQL

Tagged with ,