1. Overview
The SQL/PowerShell automation procedure is designed to perform off-site archiving of local SQL Server full and log backups in the form of compressed and encrypted files (7zip) which are being uploaded to Amazon Simple Storage Service (Amazon S3).
2. Native Backup SQL Agent Jobs
The SQL native backup process consists of the following jobs:
- System databases full backup
- User databases full backup
- User databases transaction log backup
- User databases differential backup
2.1 System Databases Full Backup (SQL Agent Job)
SQL Agent Job name: WTADMIN_BACKUP_SYSTEM_DATABASES_FULL
Creates full backup of system databases locally.
Specification and backup options:
Databases |
System Databases (Master, MSDB, TEMPDB, Model) |
Directory |
E:\Backup\DATABASE\WIN-2PKGIMKGIIA |
BackupType |
Full Backup |
Verify |
Verify the backup |
CleanupTime |
192 hours (after which the backup files are deleted) |
Compress |
Compress the backup |
CheckSum |
Enable backup checksums |
LogToTable |
Log commands to the table CommandLog |
Execute |
Execute commands |
2.2 User Databases Full Backup (SQL Agent Job)
SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_FULL
Creates full backup of user databases locally.
Specification and backup options:
Databases |
User Databases |
Directory |
E:\Backup\DATABASE\WIN-2PKGIMKGIIA |
BackupType |
Full Backup |
Verify |
Verify the backup |
CleanupTime |
96 hours (after which the backup files are deleted) |
Compress |
Compress the backup |
CheckSum |
Enable backup checksums |
LogToTable |
Log commands to the table CommandLog |
Execute |
Execute commands |
2.3 User Databases Transaction Log Backup (SQL Agent Job)
SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_LOG
Creates transaction log backup of user databases locally.
Specification and backup options:
Databases |
User Databases |
Directory |
E:\Backup\DATABASE\WIN-2PKGIMKGIIA |
BackupType |
Transaction Log Backup |
Verify |
Verify the backup |
CleanupTime |
25 hours (after which the backup files are deleted) |
Compress |
Compress the backup |
CheckSum |
Enable backup checksums |
LogToTable |
Log commands to the table CommandLog |
Execute |
Execute commands |
2.4 User Databases Differential Log Backup (SQL Agent Job)
SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_DIFF
Creates differential backup of user databases locally.
Specification and backup options:
Databases |
User Databases |
Directory |
E:\Backup\DATABASE\WIN-2PKGIMKGIIA |
BackupType |
Differential Backup |
Verify |
Verify the backup |
CleanupTime |
96 hours (after which the backup files are deleted) |
Compress |
Compress the backup |
CheckSum |
Enable backup checksums |
LogToTable |
Log commands to the table CommandLog |
Execute |
Execute commands |
3. PowerShell Tasks & Related SQL Agent Jobs
The procedure consists of the following tasks:
- PowerShell Script to encrypt and compress backup and log files locally
- PowerShell Script to copy files to Amazon S3 storage
- PowerShell Script to delete after time period on Amazon S3
- SQL Server Agent Job for scheduled execution
3.1 Encrypt and compress backup and log files locally (PowerShell script)
Filename: Bak-to-7z.ps1
Location: E:\S3BackupSync
Description: This script will leverage 7z command-line utility to compress and password encrypt SQL backup and log files.
Input folder: E:\Backup\DATABASE
The folder that contains MSSQL .bak and .trn files.
Output folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are being created.
Example of 7z archive file names:
WIN-2PKGIMKGIIA_company_DB_FULL_20160331_021500.bak.7z
WIN-2PKGIMKGIIA_company_DB_LOG_20160404_001001.trn.7z
3.2 Copy compressed files to Amazon S3 (PowerShell script)
Filename: 7z-to-S3.ps1
Location: E:\S3BackupSync
Description: This script will upload 7zip archives to Amazon S3 bucket.
Note: To work properly this script requires AWS SDK for .NET (https://aws.amazon.com/sdk-for-net/) to be installed on the local machine.
Input folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are stored.
Output location:
S3 bucket name: company-backups-new
S3 Region: us-east-1
3.3 S3 bucket obsolete files cleanup (PowerShell script)
Filename: Clean-S3-Bucket.ps1
Location: E:\S3BackupSync
Description: This script will purge obsolete files in Amazon S3 bucket.
Target location:
S3 bucket name: company-backups-new
S3 Region: us-east-1
3.4 SQL Server Agent Job for permanently scheduled execution
The process contains of three SQL Server agent jobs:

3.4.1 S3BackupCleanup
This job will run daily at 6:00:00 AM and once triggered it will call the PowerShell script “Clean-S3-Bucket.ps1” with an argument for the file retention period (in days):
An email notification regarding the job status will be send to Michael Roedeske.
Command:
powershell.exe “&E:\S3BackupSync\Clean-S3-Bucket.ps1 15”
The script will delete all files located on the S3 bucket older than 15 days.
3.4.2 S3BackupSyncFull
This job consist of two steps:

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database full backups files (.bak).
Command:
powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 bak”
The PowerShell script is being called with an argument “bak” in order to process only SQL full backups.
Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .bak files to S3 bucket.
Command:
powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 bak”
The PowerShell script is being called with an argument “bak” in order to process only SQL full backup archives.
An email notification regarding the job status will be send to the specified email address.
3.4.3 S3BackupSyncLog
This job also consist of two steps:

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database transaction logs (.trn).
Command:
powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 trn”
The PowerShell script is being called with an argument “trn” in order to process only SQL transaction logs.
Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .trn files to S3 bucket.
Command:
powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 trn”
The PowerShell script is being called with an argument “trn” in order to process only SQL transaction log archives.
An email notification regarding the job status will be send to the specified email address.
4. Additional files/tools

7z.dll, 7z.exe – 7zip required files
7zip.bin – 7z encrypted password file
PasswordMgr.exe – S3 credentials and 7z password manager
Password Manager GUI tool has been designed to securely store the 7zip encryption password and S3 account keys.

7zip password is encrypted and stored into 7zip.bin file.
S3 Access and Secret keys are stored into local secure store and loaded into memory when needed.
The file is located in %LOCALAPPDATA%\AWSToolKit\RegisteredAccounts.json and the keys are obfuscated- either hashed or encrypted.
Note: The stored credentials can be used only by the same user who created the store. %USERPROFILE% environmental variable must be set.