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

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.

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.).

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:

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