SQL Server Backup Script for AWS RDS
Jump to navigation
Jump to search
DECLARE @dbs TABLE (dbname VARCHAR(50)) DECLARE @currentDB VARCHAR(50) declare @SQL1 nvarchar(3000) declare @SQL2 nvarchar(3000) declare @BUCKET nvarchar(200) -- change the bucket name based on environment set @BUCKET='arn:aws:s3:::ee-development-db-backups' DECLARE @DATE AS VARCHAR(10) SET @DATE= CONVERT(VARCHAR(8),GETDATE(),112) INSERT INTO @dbs (dbname) SELECT [Name] FROM master.sys.databases WHERE name not in ('master','model','msdb','tempdb','rdsadmin') WHILE EXISTS (SELECT dbname FROM @dbs) BEGIN SET @currentDB = (SELECT TOP 1 dbname FROM @dbs ORDER BY dbname) SET @SQL1='exec msdb.dbo.rds_backup_database @source_db_name=''' + @currentDB + ''', @s3_arn_to_backup_to=''' + @BUCKET + '/14DY_' + @currentDB + '_' + @DATE + '.bak'', @overwrite_S3_backup_file=1;' SET @SQL2='exec msdb.dbo.rds_backup_database @source_db_name=''' + @currentDB + ''', @s3_arn_to_backup_to=''' + @BUCKET + '/7YR_' + @currentDB + '_' + @DATE + '.bak'', @overwrite_S3_backup_file=1;' if day(getdate()) = '01' exec sp_executesql @SQL2 else exec sp_executesql @SQL1 DELETE FROM @dbs WHERE dbname = @currentDB END