SQL Server (RDS) Native Backup/Restore

Hello Friends!

AWS RDS (SQL Server) Native Backup & Restore

Back to you with my another blog talks simple things about SQL Server Instances hosted as DBaaS (Database As A Service) in Amazon cloud. Last year Amazon launch a vital feature i.e. native backup & restore SQL Server (& other RDBMS) databases hosted as RDS (Relational Database Services) with the help of S3  (Simple Storage Service).

Found this feature extremely useful and using day in day out. Only catch over here, SSMS doesn’t work here due to limited permissions. So T-SQL remains only option. Does it really matter, Nah! As far as most of us able to use this feature at great extent, any method gives an ease.

Native Backup of SQL Server RDS

Open a “Query Window” in SSMS and run below commands for backup, if request is for backing up database in S3 bucket. Don’t forget to change @source_db_name & ……-sqls3 based upon environment.

exec msdb.dbo.rds_backup_database 
      @source_db_name='LoadTest', 
@s3_arn_to_backup_to='arn:aws:s3:::test-account-sqls3/LoadTest.BAK',
@overwrite_S3_backup_file=1;

Note: @overwrite_S3_backup_file=1; means every execution backup file will be overwritten.

Result

1 - aws_rds_native_backup

Keep in mind, AWS RDS doesn’t support native T-SQL Backup commands for SQL Server databases. Only aforesaid command works to take backups in S3 bucket.

Verify backup progress of SQL Server RDS

Verifying a SQL Backup is one of the very tingling job for every SQL DBA. Whether DBA knows it’s going to take hell lot of time or just a flicker of eyes, running T-SQL or peeping progress bar is top most. So do I, but SQL Server RDS native backup progress is monitored by single method i.e. T-SQL. So let’s monitor what’s status of backup which is currently running.

exec msdb.dbo.rds_task_status @db_name='LoadTest'
OR
exec msdb.dbo.rds_task_status @task_id = 1

Note: @task_id is available in first column of backup result set. Please refer first screenshot.

2 - aws_rds_backup_progress

Cancel Native backup of SQL Server RDS

 Above result set’s first column is “task_id”. All you need to pick respective task ID for unintended backup command and execute below command to cancel backup.

exec msdb.dbo.rds_cancel_task @task_id=1;

Incase respective task ID is completed, below error is resulted.

Msg 50000, Level 16, State 0, Procedure rds_cancel_task, Line 17

Cannot find a CREATED or an IN_PROGRESS task: 1 to cancel.

Post completion of backup (look for “% complete” of aforesaid command result set) backup file is present in appropriate S3 bucket for further action (download/restore/etc.).

3 - aws_rds_S3 backup

Restore Native backup of SQL Server RDS

Open a “Query Window” in SSMS and run below commands for backup, if request is to restore database from S3 bucket. Don’t forget to change @source_db_name & ……-sqls3 based upon environment.

exec msdb.dbo.rds_restore_database 
@restore_db_name='LoadTest_Restore', 
@s3_arn_to_restore_from='arn:aws:s3:::test-account-sqls3/LoadTest.BAK';

Note: There is no method available so far to change the target data & log file location, like most of us do under in-premise environment. Default location set up by AWS RDS are final.

Result:

4 - aws_rds_native_restore

Restoration progress gets monitored with same command like backup progress monitoring.

Note: You can’t restore a backup file to the same DB instance that  used to create the backup file. Instead, restore the backup file to a new DB instance. Even renaming the database is not a workaround for this limitation.

Happy Learning!

Thank you!
Avanish Panchal
Regional Head – DataPlatformGeeks & DPS2017 Core Team Member
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

Avatar

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

One Comment on “SQL Server (RDS) Native Backup/Restore”

  1. What I do to overcome the limitation of RDS to allowing for restoring to the same DB instance and even the same database is:

    1. Backup my local database.
    2. Restore my local backup with SSMS to a new database and at the time change just the name (not the path nor the extensions) of the .mdf and .ldf files from ‘my_db’ to something like ‘my_db_20180406’.
    3. Do the restore.
    4. Backup the new database just created.
    5. Upload the backup to AWS S3.
    6. Restore from S3 with the rds stored procedures, overwriting the existing database.
    7. That’s all.

Leave a Reply

Your email address will not be published. Required fields are marked *