Archive for the ‘Scripting’ Category

Download and delete files from FTP using Powershell

The following Powershell script connects to a FTP server, gets a list of files from a specific subfolder, downloads and deletes them.

It uses the System.URI,,, System.Net.NetworkCredential, System.IO.StreamReader and other .Net classes.

$local_target1 = 'D:\997\'

function GetFilesListAsArray($user,$pass,$local_target,$ftp_uri,$subfolder)
 # ftp address from where to download the files
 $ftp_urix = $ftp_uri + $subfolder
 $uri=[system.URI] $ftp_urix


 $ftp.Credentials=New-Object System.Net.NetworkCredential($user,$pass)
 #Get a list of files in the current directory.
 #Use ListDirectoryDetails instead if you need date, size and other additional file information.

 $reader=New-Object System.IO.StreamReader($strm,'UTF-8')
 return $lines
 $_|fl * -Force

function DownloadFile ($sourceuri,$targetpath,$username,$password){
 # Create a FTPWebRequest object to handle the connection to the ftp server
 $ftprequest = [System.Net.FtpWebRequest]::create($sourceuri)

# set the request's network credentials for an authenticated connection
 $ftprequest.Credentials = New-Object System.Net.NetworkCredential($username,$password)

$ftprequest.Method = [System.Net.WebRequestMethods+Ftp]::DownloadFile
 $ftprequest.UseBinary = $true
 $ftprequest.KeepAlive = $false

# send the ftp request to the server
 $ftpresponse = $ftprequest.GetResponse()

# get a download stream from the server response
 $responsestream = $ftpresponse.GetResponseStream()

# create the target file on the local system and the download buffer
 $targetfile = New-Object IO.FileStream ($targetpath,[IO.FileMode]::Create)
 "File created: $targetpath"
 [byte[]]$readbuffer = New-Object byte[] 1024

# loop through the download stream and send the data to the target file
 $readlength = $responsestream.Read($readbuffer,0,1024)
 while ($readlength -ne 0)

 $_|fl * -Force

function DeleteFile ($file,$username,$password){
 # Delete files from FTP server after download
 $uri2=[system.URI] $file
 $request = [system.Net.FtpWebRequest]::Create($uri2)
 $request.Credentials = New-Object System.Net.NetworkCredential($username,$password)
 $request.Method = [System.Net.WebRequestMethods+FTP]::DeleteFile
 $response = $request.GetResponse()
 "Deleted from FTP: $source" -f $response.StatusDescription

$lines = GetFilesListAsArray $user1 $pass1 $local_target1 $ftp_uri1 $subfolder1
'List of files OK'
$total_files = 0
foreach ($line in $lines){
 if ($line -ne '') {
 #TRIM the file_name because if it contains blank spaces you will get errors when creating the file
 $file_name = $line.ToString().Trim()
 $source = $ftp_uri1 + $subfolder1 + $file_name
 $target = $local_target1 + $file_name
 DownloadFile $source $target $user1 $pass1
 DeleteFile $source $user1 $pass1
 $total_files = $total_files + 1
"Total files downloaded: $total_files"

If you get an error like the following when trying to execute the script:

File C:\scripts\test.ps1 cannot be loaded because the execution of scripts is disabled on this system.
Please see “get-help about_signing” for more details.

Make sure the execution of Powershell scripts is enabled:


And set it to the appropriate level:

Set-ExecutionPolicy Unrestricted


Running Windows PowerShell Scripts

Using the Set-ExecutionPolicy Cmdlet

(Powershell) Loop to delete files from an FTP Location


Powershell: backup and compress SQL databases

The following Powershell script code backups all databases for a specific SQL Server and then compress those files (*.bak) into a ZIP file.

The last line is very important, because it lists the “.bak” files in the directory, ordered by length (ascending), so the compression process doesn’t get interrupted.

Lately, you can call this script from a BAT file and set it up as a scheduled task, so, let’s say, backup all your databases from your server everyday at midnigth, and compress them to a zipped file. Next morning when you arrive to your office, you can download it. Even more, you can schedule it to download it automatically to an FTP server at your data center.

$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
# sql server version is 2008 or later, also load these other assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null

$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)" #this can also be "SERVERNAME\INSTANCENAME"

$bkdir = "E:\Backups" #We define the folder path as a variable
$dbstobackup = @(“DB1", “DB2", “DB3")

$dbs = $s.Databases
foreach ($db in $dbs)
if($dbstobackup -contains $db.Name)
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")

# Create a new zip file from pipeline
function Create-Zip()

New-Zip -zipfileName $zipFile
$zip = Get-Zip -zipfileName $zipFile

#loop through files in pipeline
foreach($file in $input)
#add file to zip and sleep 1/2 second
Start-sleep -milliseconds 15000


#create a new zip file
function New-Zip
set-content $zipfilename ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
(dir $zipfilename).IsReadOnly = $false

#get the zip file
function Get-Zip
$shellApplication = new-object -com shell.application
$zipFullFilename = (get-childitem $zipfilename).FullName

$dtzip = get-date -format yyyyMMddHHmm
$zipfilename = $bkdir + "\DB_Backups_" + $dtzip + ".zip"
Get-ChildItem -path $bkdir -filter *.bak* | Sort-Object length | create-zip $zipfilename


Backup SQL Server Databases with a Windows PowerShell Script

Loading SMO Assemblies into PowerShell

PowerShell Tips