Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Archive for the ‘Powershell’ 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

Upload of files to FTPS from PowerShell

leave a comment »

Recently I’ve updated my PowersShell script to upload files to FTPS (FTP over SSL) server. Below you can find that script. Most important change in comparison to previous one is around the way I read and send data. In previous version this was done thru read of entire file into memory and then just sent it from the buffer. That of course could cause problems in case of large files. New approached uses small buffer (=4096) and send that in peace’s. To find more details about that script please refer to my previous post

[Net.ServicePointManager]::ServerCertificateValidationCallback={$true}
$Dir = "D:\FolderWithBackupFilesToMove"
foreach($item in (dir $dir))
{
    write-output "————————————–"
    $fileName = $item.FullName
    write-output $fileName
    $ftp = [System.Net.FtpWebRequest]::Create("ftp://some.ftp.server.com/someRootFolder/"+$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()
   
    $reader = New-Object System.IO.FileStream ($fileName, [IO.FileMode]::Open, [IO.FileAccess]::Read, [IO.FileShare]::Read)
    [byte[]]$buffer = new-object byte[] 4096
    [int]$count = 0
    do
    {
        $count = $reader.Read($buffer, 0, $buffer.Length)
        $rs.Write($buffer,0,$count)
    } while ($count -gt 0)
    $reader.Close()
    $rs.Close()
    write-output "+transfer completed"
   
    $item.Delete()
    write-output "+file deleted"
}

have fun

Written by Mariusz Gorzoch

7 January 2014 at 11:07

Posted in Powershell

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 ,

XmlAttribute & PowerShell

leave a comment »

I was trying today to retrieve attribute value from Powershell and found that for some reason $a.InnerXml, $a.OuterXml, $a.Value is note returning any value. As this is quite wired I started to dig and found the way how the value should be retrieved:

$a.get_OuterXml() or a$.get_InnerXml()

this is wired, but the way above works.

Written by Mariusz Gorzoch

12 March 2010 at 10:01

Posted in Powershell

How to send email with use of PowerShell

leave a comment »

I’m quite sick of looking for this info everytime I need it, so this time I decided to create post about it, so here it is. To send email with use of PowerShell you just need to issue those commands:

$emailFrom = "user@yourdomain.com"
$emailTo = "user@yourdomain.com"
$subject = "your subject"
$body = "your body"
$smtpServer = "your smtp server"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body)

this is quite easy and usefull especialy if you need to check if the server where you are installing your app allow you to send emails (for some reasons this functionaliy is quite often blocked inside organizations).

Written by Mariusz Gorzoch

18 July 2009 at 08:46

Posted in Powershell