Skip to content

Instantly share code, notes, and snippets.

@marcelobrake

marcelobrake/backup_restore_mssql_rds_s3.md

Last active April 30, 2020 11:56
Show Gist options
  • Save marcelobrake/6ba39e8ccddeae00d93d0d74c7eaf846 to your computer and use it in GitHub Desktop.
Save marcelobrake/6ba39e8ccddeae00d93d0d74c7eaf846 to your computer and use it in GitHub Desktop.
Download ZIP
Backup/Restore SQL Server on RDS to S3 #AWS #RDS #S3 #MSSQL
Raw
backup_restore_mssql_rds_s3.sql
-- Para fazer um backup/restore do banco SQL Server do RDS em um bucket S3 será necessário executar os seguintes passos:
-- 1. Criar o bucket no S3 na mesma região onde está o banco (ex: Conta: My_Account, região: sa-east-1, bucket: my-backup-bucket)
-- 2. No RDS, criar ou editar um "Option Group" adicionando a opção SQLSERVER_BACKUP_RESTORE, setando o bucket em questão
-- *******************************************************************************************************************************************
-- Comando para iniciar o backup (substitua com os valores nas variáveis)
-- *******************************************************************************************************************************************
USE [msdb]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[rds_backup_database]
@source_db_name = 'your_database_name',
@S3_arn_to_backup_to = 'arn:aws:s3:::your-bucket-name/folder/db.bak',
@KMS_master_key_arn = NULL,
@overwrite_S3_backup_file = NULL
SELECT 'Return Value' = @return_value
GO
-- *******************************************************************************************************************************************
-- Verificar o progresso do backup/restore (substitua com os valores nas variáveis)
-- *******************************************************************************************************************************************
USE [msdb] GO
DECLARE @return_value int
EXEC @return_value = [dbo].[rds_task_status] @db_name =
'your_database_name', @task_id = <<<found in result of previous query>>>
SELECT 'Return Value' = @return_value
GO
-- *******************************************************************************************************************************************
-- Comando para fazer o restore (substitua com os valores nas variaveis)
-- *******************************************************************************************************************************************
exec msdb.dbo.rds_restore_database
@restore_db_name='database_name',
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/folder/file_name_and_extension';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment