In this post I’ll show an example of simple automation task – we need to download 7z archive from SFTP, unzip it and restore the newest database automatically on a daily basics.

If we go bit deeper we’ll see a few more requirements:

1. SFTP requires auth;
2. 7z file is protected with password;
3. We need to test every important step;
4. We need to add permissions to a database;
5. We can shrink transaction log to save some space;
6. We we need to log every important step to EventLog;
7. We want to get a nice report with time measures;

It’s clear now, so let’s get down to prerequisites.

Because archive is 7z file, we need to install 7-zip, here’s link for it – http://7-zip.org/a/7z1514-x64.msi

Also we need to install Posh-SSH module to work with SFTP:

wget https://gist.github.com/darkoperator/6152630/raw/c67de4f7cd780ba367cccbc2593f38d18ce6df89/instposhsshdev | iex

We don’t want to keep credentials for SFTP & SMTP as plain text, so let’s save ’em encrypted:

Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File E:\SecFolder\...

And of course we can execute this script on remote host:

Invoke-Command { powershell.exe -noprofile C:\Users\dk\Documents\db_restore_from_sftp-public\db_restore_from_sftp.ps1 } -ComputerName db01

So far we’re ready to see the script:

[pastacode lang=”markup” path_id=”65386622ea59d376b96809ed6ffae1ab” file=”db_restore_from_sftp.ps1″ highlight=”” lines=”” provider=”gist”/]

I hope this info will be useful for you, and if you need any help feel free to use contact from on the main page.