Upload Backup Files to Amazon S3

,

This script queries msdb for the latest backup file for each user database, of the type specified by the @backupType parameter , and then pushes the file to an Amazon Web Services (AWS) S3 bucket using an embedded PowerShell statement.

Before use the @AWS... variables need to be set with the correct keys, region and bucket name. The AWS PowerShell tools also need to be installed on the server, available from http://aws.amazon.com/powershell/, and xp_cmdshell must be enabled.

The procedure is created with the WITH ENCRYPTION option to protect the access key and secret key.

The script can be easily extended to also cater for differential backups and copying the system database backups as well, and the @AWS... variables could also be parameterised as an option (things that weren't necessary for our scenario).

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

Rate

5 (1)

Share

Share

Rate

5 (1)