Download and restore MS SQL database backup

databaseSchedulingIconIn 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 –

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

wget | 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.