Alex Marsh, 2014-11-14 (first published: 2014-09-14)
This article has been shared from sqlservercentral.com site
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 NULLBEGINDROP PROCEDURE dbo.usp_CopyBackupToS3;END
GO
CREATE PROCEDURE dbo.usp_CopyBackupToS3 @backupType VARCHAR(4) WITH ENCRYPTION
ASBEGINSET 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 bucketSET @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 cmdletSET @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 copyDECLARE curBackups CURSOR FORselect 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'ENDAND d.database_id > 4) a where a.rn = 1;OPEN curBackups;FETCH NEXT FROM curBackups INTO @fileName;WHILE @@FETCH_STATUS = 0BEGIN-- add the backup file name to the PowerShell commandSET @psCmd = @cmd + ' -File ' + @fileName + ' -key ' + @fileName;--print @psCmd; --Debug: Uncomment this line and comment out the exec line to debug-- Execute the PowerShell commandexec xp_cmdshell @psCmd;FETCH NEXT FROM curBackups INTO @fileName;ENDCLOSE curBackups;DEALLOCATE curBackups;END