CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-s3database-backupsrds
María Cristina Fernández López
María Cristina Fernández López

Reputation: 437

Automate RDS backup SQL Server to S3

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?

Upvotes: 2

Views: 4055

Answers (2)

dbz
dbz

Reputation: 26

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

Upvotes: 1

ThomasVdBerge
ThomasVdBerge

Reputation: 8140

RDS snapshots and backups are not something you store directly -- RDS stores it for you, on your behalf -- so there is no option to select the bucket or region: it is always stored in an S3 bucket in the same AWS region where the RDS instance is located. This can't be modified.

The data from RDS backups and snapshots is not visible to you from the S3 console, because it is not stored in one of your S3 buckets -- it is stored in a bucket owned and controlled by the RDS service within the region.

If you want to run your script automatically I would suggest launching a small ec2 instance and install it as a cron.

Upvotes: 1

Related Questions