I have a SQL Server running on AWS RDS. I currently have a S3 bucket where I store the backups that I generate through SQL Server Management Studio with the following script:
exec msdb.dbo.rds_backup_database
@source_db_name='database_name',
@s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
@overwrite_S3_backup_file=1;
The problem is that I have to perform these backups manually.
Is there any way to automate this script to run for example, every day?
Also, through RDS backups are performed automatically every day, but these backups are not visible through my S3 bucket.
Is there any way that these automatic backups are stored in my s3?
To automate the execution of the AWS RDS Native Backup (or Restore). Create a SQL Agent Job with a single step that is the stored procedure call. It can then be run on demand or scheduled by adding a schedule to the job. An example job can be seen below, but can also be defined in SSMS. (This was done in version 13.0.5292.0)
USE [msdb]
GO
/****** Object: Job [db_export_to_aws_s3] Script Date: 7/30/2019 12:03:45 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/30/2019 12:03:45 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'db_export_to_aws_s3',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Export job to send database backup to AWS S3.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'rds_admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [aws_rds_native_backup_to_s3] Script Date: 7/30/2019 12:03:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'aws_rds_native_backup_to_s3',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec msdb.dbo.rds_backup_database
@source_db_name=''MY_TEST_DB'',
@s3_arn_to_backup_to=''arn:aws:s3:::test_aws_s3_bucket/MY_TEST_DB-dev.bak'',
@overwrite_S3_backup_file=1;',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyDatabaseBackup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190712,
@active_end_date=99991231,
@active_start_time=50000,
@active_end_time=235959,
@schedule_uid=N'67a62f634-d4657-4d9c-56bc-c34569278e8c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO