Powershell Backup All Databases Locally and to Amazon S3

Previously I had posted a Powershell script that would use 7Zip to compress and encrypt a folder and then send it to Amazon S3. This script could have many uses, but in my case it was designed to fulfill the need for off site storage of compressed database backups. While it did all the heavy lifting of getting the data encrypted, compressed and uploaded to S3 I left the creation of the database backup folder as an exercise for the user to complete.

I now have a working system that I would like to share in case anyone was unable to create the backup folder using Powershell. There were a few hiccups that I found, such as not being able to use the Start-Transcript method in a SQL Agent Job and having my MSDB data file triple in size after 3 months of backups, but after working out those issues the script provides a very cost effective method of creating both local and offsite database backups with 1 week or 1 month retention. Currently I have it running on a server with about 100 databases ranging in size from 7MB to 700MB with daily full backups and hourly differential backups being sent to Amazon S3 and stored with 1 month retention for about $5 a month. The total raw backup size is 2.2GB per full backup set and 100-400MB for each differential backup set, but the script gets about 10:1 compression so the daily storage on S3 is about 500MB total for all of the full and differential backups.

You will need to modify the settings in both the psBackupAllDBtoAmazonS3.ps1 and ps7ZiptoAmazonS3.ps1 files to get this to work on your server, but once you do you can sit back and let the scripts do it’s magic. You can set it to run as a scheduled task, but we chose to run it as an SQL Agent Job so that our server monitoring software would notify us if there were any issues with the script.

#Description: Powershell 2.0 script to backup all databases in MS SQL Server to the local disk and then zip and encrypt the folder using 7zip and then send it to Amazon S3 for offsite storage. #Author: Greg Bray (6/14/2010) #Website: http://codeblog.theg2.net/2010/06/powershell-backup-all-databases-locally.html #License: free for personal or commercial use under the Creative Commons Attribution 3.0 United States License. #Prerequisite: You must first install the latest version of 7Zip from http://www.7-zip.org/ (licensed under the GNU LGPL) #Prerequisite: You must also install the Amazon Web Services SDK for .NET from http://aws.amazon.com/sdkfornet/ (licensed under the Apache License, Version 2.0) #Prerequisite: You must also download the ps7ZiptoAmazonS3.ps1 from http://codeblog.theg2.net/2010/02/powershell-7-zip-amazon-s3-upload.html #Prerequisite: You must set the settings listed below as well as the settings listed in the ps7ZiptoAmazonS3.ps1 file #Recommended: I recommend using CloudBerry S3 Explorer for managing Amazon S3 files. http://cloudberrylab.com/default.aspx?page=cloudberry-explorer-amazon-s3 #Recommended: I recommend using http://PowerGui.org Script editor if you want to change or debug this script. <#Example calling from Run, a batch file, SQL Agent Job, or the task Scheduler: NOTE: may need to run "Set-ExecutionPolicy RemoteSigned" in powershell to allow unsigned local scripts to execute powershell.exe -Command "& 'c:\Backup\psBackupAllDBtoAmazonS3.ps1'" #> #Script will backup all active databases on the server except those listed on an exclusion list. #Performs Differential backup except when $bFullBackup is true (currently set to do full backup when run between 00:00 and 01:00) #Uses a 1 week local retention and either a 1 week or 1 month offsite retention on Amazon S3 depending on the definition of $S3FilePath #Offsite files will be compressed and encrypted using 7zip. Local files will not be compressed or encrypted. #There also will be a transcript log created that can be used to monitor the script. NOTE: If run using SQL Agent Job Start-Transcript will not work. $strLogfile = "c:\Backup\Backup Log.rtf" if (($host.Name -notmatch '^PowerGUI') -and ($host.name -ne "Windows PowerShell ISE Host")) {#PowerGUI and ISE Hosts do not support transcriptions yet :-( #Start-Transcript $strLogfile -Append | Out-Null #Append output to log file. Disabled as SQL Agent Jobs don't work with powershell transcripts #NOTE: Transcripts don't work with SQL Agent Jobs either... See http://stackoverflow.com/questions/2383605/powershell-transcript-is-empty-when-running-script-from-sql-agent-job-in-2005-sql } #Settings $strBackupPathBase = "c:\Backup" #Base path on server where to store backups. NOTE: Do not include trailing slash. $arrDBsToExclude = @("tempdb", "master") #DBs not included in the backup set. NOTE: Master database does not support differential backups, but will be included in full backups (see $strBackupQuery definition below) $dtStart = [DateTime]::Now #capture start date to track total time $bFullBackup = ($dtStart.Hour -eq 0) #Perform full backup instead of differential when run between 00:00 and 01:00 $strDateTimeStamp = $dtStart.ToString("yyyy-MM-dd_HHmm").ToUpper() #Timestamp appended to all backup files. NOTE: this is not the actual backup time, but when the script starts running $strBackupPath = "{0}\{2}-{1:ddd}\{1:HH}" -f $strBackupPathBase, $dtStart, $dtStart.DayOfWeek.value__ #Full path where files will be stored locally (1 week retention). If folder exists it will be deleted #See $S3FilePath and DB Connection string definitions below. Also update encryption settings in ps7ZiptoAmazonS3.ps1 Write-Host ("`r`n`r`n{0} - Starting SQL Server Differential backup with these parameters:`r`n`tBackupPath='{1}'`r`n`tTimestamp={2}`r`n`tFullBackup={3}`r`n`tExclude Databases: ${arrDBsToExclude}" -f [DateTime]::Now, $strBackupPath,$strDateTimeStamp,$bFullBackup) #Delete backup directory if it already exists. This creates a 1 week local retention for the backup files if([System.IO.Directory]::Exists($strBackupPath)){ Remove-Item $strBackupPath -Force -Recurse } New-Item $strBackupPath -type directory | Out-Null #Create directory again #Setup database connection $conn = new-object System.Data.SqlClient.SqlConnection $conn.ConnectionString = 'Data Source=localhost;Initial Catalog=master;User ID=sa;Password=SAPASSWORD' #NOTE: use ' instead of " to prevent issue with $ character if($conn.State -ne [System.Data.ConnectionState]::Open){ $conn.Open();} #Open connection if not already open #Get a list of all databases $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= "SELECT name FROM sys.databases" $sqlReader = $cmd.ExecuteReader() $lstDatabases = New-Object System.Collections.ArrayList while ($sqlReader.Read() -eq $true){ [void]$lstDatabases.Add($sqlReader.GetString(0)) #Add database name to list } $sqlReader.Close() #Backup all databases to disk $strBKType = "DIFF" $intBackups = 0 $strBackupQuery = "BACKUP DATABASE [{0}] TO DISK = '{1}' WITH DIFFERENTIAL, INIT" #0: Database name, 1: Backup file path if($bFullBackup){# Perform full backup instead of differential $strBackupQuery = "BACKUP DATABASE [{0}] TO DISK = '{1}' WITH INIT" #0: Database name, 1: Backup file path $arrDBsToExclude = @() + ($arrDBsToExclude | where {$_ -notmatch "master"}) #make sure master database is included in the full backup $strBKType = "FULL" } foreach($strDatabase in $lstDatabases){ if($arrDBsToExclude -notcontains $strDatabase){ #write-Host ("`r`n{0} - Backing up database: {1}" -f [DateTime]::Now, $strDatabase) $strFilename = "{0}\{1}_{2}_{3}.bak" -f $strBackupPath, $strDatabase, $strBKType, $strDateTimeStamp $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= $strBackupQuery -f $strDatabase, $strFilename $intBackups += 1 $intReturn = $Null $intReturn = $cmd.ExecuteNonQuery() if ($intReturn -eq $Null) { write-Host ("{0} - Database {1} backup failed ({2} of {3}). Retry after 10 seconds." -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) Start-Sleep -s 10 $intReturn = $cmd.ExecuteNonQuery() if ($intReturn -eq $Null) { write-Host ("{0} - Database {1} backup failed ({2} of {3}). Script aborted." -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) exit 2 #Quit script if there is an error } } #write-Host ("{0} - Database {1} backed finished successfully ({2} of {3})" -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) } } #Perform basic monthly db maintinece here. Will execute on first day of the month when the full backup is created if($bFullBackup -and ($dtStart.Day -eq 0)){ #Clean up MSDB backup history logs to prevent excessive growth of MSDB. See http://technet.microsoft.com/en-us/library/ms188328.aspx write-Host ("{0} - Start server maintinence: sp_delete_backuphistory on MSDB" -f [DateTime]::Now) $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= 'use msdb; Declare @date datetime; Set @date = dateadd (mm, -1, getdate()); Exec sp_delete_backuphistory @date' $Null = $cmd.ExecuteNonQuery() } if($conn.State -eq [System.Data.ConnectionState]::Open){ $conn.Close();} #Close database connection #Call powershell script to encrypt data and send it to Amazon S3. NOTE: there are other settings configured in the script file. #$S3FilePath = "DB2/{2}-{0:ddd}/Hour_{0:HH}_{1}_Backup.7z" -f $dtStart,$strBKType,$dtStart.DayOfWeek.value__ #S3 path using DB2 bucket and 1 week retention $S3FilePath = "DB2/{0:dd}/Hour_{0:HH}_{1}_Backup.7z" -f $dtStart,$strBKType #S3 path using DB2 bucket and 1 month retention & 'C:\Backup\ps7ZiptoAmazonS3.ps1' "$strBackupPath" "$S3FilePath" $dtEnd = [DateTime]::Now $tsTotal = $dtEnd.Subtract($dtStart) #Get total processing time write-Host ("`r`n`r`n{0} - Finished SQL Server Backup. Total processing time: {1}`r`n`tBackup Path:{5}`r`n`tS3 Storage Path:{7}`r`n`tFull Backup:{6}`r`n`tTotal databases backed up: {2} of {3}`r`n`t{4} Exclusions: - ${arrDBsToExclude} " -f $dtEnd,$tsTotal,$intBackups,$lstDatabases.Count,$arrDBsToExclude.Count, $strBackupPath, $bFullBackup, $S3FilePath) if (($host.Name -notmatch '^PowerGUI') -and ($host.name -ne "Windows PowerShell ISE Host")){ #Stop-Transcript #Disabled for use with SQL Job. } exit $LASTEXITCODE #Make sure to pass exitcode from ps7ZiptoAmazonS3 to host for error tracking