How to await the execution of the rds_backup_database stored procedure?

March 3, 2022 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

Our team is migrating one of our databases to Amazon RDS MS SQL Server. As part of that, we have to rewrite some of our stored procedures.

We have a stored procedure that performs a database backup to a network storage.

BACKUP DATABASE [MyDB] TO DISK = '\\myserver\share\MydbBackup.bak'
WITH RETAINDAYS = 0,
    INIT

The above stored procedure is blocking i.e., it will pause the line of execution – exec stored_proc – until the backup is completed.

I have changed that stored procedure so that it uses the rds_backup_database stored proc so that the backup is saved in an S3 bucket.

exec msdb.dbo.rds_backup_database
    @source_db_name='MyDB',
    @s3_arn_to_backup_to='arn:aws:s3:::temp-poc-bucket/db_backups/MyDBBackup.bak',
    @overwrite_s3_backup_file=1;

The problem is that our workflow (the stored procedure is called from our app) requires it to be synchronous.

Is there any way to "await" for the execution of the msdb.dbo.rds_backup_database stored procedure?

AWS RDS docs.

asked 2022-03-03 by Georgi Koemdzhiev

Answer

Per the docs:

When the rds_backup_database or rds_restore_database stored procedure is called, the task starts and outputs the information about the task.

…
When the lifecycle status of the task is SUCCESS, the task is complete.

…
You can get the Task ID after you perform the backup or restore statement. Or, you can use the following script to identify all the completed and pending tasks for a particular database:

exec msdb.dbo.rds_task_status @db_name='database_name'

To track the status of the job, use this SQL statement:

exec msdb..rds_task_status @task_id= 5

So, after initiating the backup with rds_backup_database, you would just need to enter a loop where you use rds_task_status to find the current status, and if it is not "SUCCESS", you could use WAITFOR DELAY '00:00:30' to sleep for 30 seconds, before continuing your loop to check status again & repeat.

There is also this answer on Stack Overflow that implements a similar solution from PowerShell. Whether you implement your code in PowerShell or T-SQL, the logic for checking & looping would be similar.

answered 2022-03-03 by Andy Mallon