Upload Backup Files to Amazon S3

use master;
go
/* Procedure dbo.usp_CopyBackupToS3
 *
 * Copies last backups of user databases to AWS S3 bucket
 *
 * Parameter: @backupType
 *            The backup to copy to S3. value: FULL or LOG
 *
 * Notes: Procedure is encrypted to protect AWS key values in script
 *  Requires AWS PowerShell tools installed on server and xp_cmdshell active
 *
 * Usage: exec master.dbo.usp_CopyBackupToS3 @backupType = 'FULL';
*/IF OBJECT_ID('dbo.usp_CopyBackupToS3', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.usp_CopyBackupToS3;
END
GO

CREATE PROCEDURE dbo.usp_CopyBackupToS3 @backupType VARCHAR(4) WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;

DECLARE @fileName VARCHAR(255);
DECLARE  @AWSAccessKey VARCHAR(128), @AWSSecretKey NVARCHAR(128);
DECLARE @AWSregion VARCHAR(128), @AWSbucketName NVARCHAR(255);
DECLARE @cmd VARCHAR(255), @psCmd VARCHAR(1000);

-- Set the access and secret keys for the bucket
SET @AWSAccessKey = '<access key>';
SET @AWSSecretKey = '<secret key>';
SET @AWSbucketName = '<bucket>';
SET @AWSregion = '<AWS region, e.g. ap-southeast-2>';

-- Create the base PowerShell statement using the Write-S3Object cmdlet
SET @cmd = 'powershell -ExecutionPolicy RemoteSigned Write-S3Object'
SET @cmd += ' -BucketName ' + @AWSbucketName;
SET @cmd += ' -AccessKey ' + @AWSAccessKey;
SET @cmd += ' -secretKey ' + @AWSSecretKey;
SET @cmd += ' -Region ' + @AWSregion;

-- Get the filenames of the most recent backups for user databases
-- The evil cursor is used to execute the PowerShell statement that does the copy
DECLARE curBackups CURSOR FOR
select physical_device_name
from (
select  ROW_NUMBER() OVER(PARTITION BY bs.database_name ORDER BY backup_finish_date DESC) as rn
, bs.database_name, bmf.physical_device_name, type
from msdb.dbo.backupset bs
join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
join sys.databases d on bs.database_name = d.name
where bs.[type] = 
CASE WHEN @backupType = 'FULL' THEN 'D'
 WHEN @backupType = 'LOG' THEN 'L'
 ELSE 'Z'
END
AND d.database_id > 4
) a where a.rn = 1;

OPEN curBackups;

FETCH NEXT FROM curBackups INTO @fileName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- add the backup file name to the PowerShell command
SET @psCmd = @cmd + ' -File ' + @fileName + ' -key ' + @fileName;

--print @psCmd; --Debug: Uncomment this line and comment out the exec line to debug

-- Execute the PowerShell command
exec xp_cmdshell @psCmd;

FETCH NEXT FROM curBackups INTO @fileName;
END

CLOSE curBackups;
DEALLOCATE curBackups;
END