Wait for AWS RDS native sql backup to be finished

Question

When creating an EC2 or RDS instance in batch or powershell, you can run the built-in aws rds wait db-instance-available --db-instance-identifier 'myDbInstance' to wait for it to be available before running further commands against it.

I have a script that restores an RDS snapshot into a new instance ($devInstanceDnsName), and then backup a single database from that instance (in MS SQL) to S3. In powershell, to run the native backup, you run sqlcmd -X -b -S "tcp:$devInstanceDnsName,1433" -d dbName -U dbUser -P dbPwd -Q "exec msdb.dbo.rds_backup_database @source_db_name='dbName', @s3_arn_to_backup_to='arn:aws:s3:::backupsbucket/$backupFileName', @overwrite_S3_backup_file=1".

That is an asynchronous task, it schedules the backup and returns immediately. But how can the script know when the native backup has completed, and continue execution, synchronously?

Answer 1

I've done a simple powershell function called like this:

Wait-RdsNativeBackupSuccess -server "$devInstanceDnsName,1433" -database dbName -user dbUser -pwd dbPwd

and defined like this:

function Wait-RdsNativeBackupSuccess($server, $database, $user, $pwd)
{
    $startDate = Get-Date
    $timeOutminutes = 45
    $retryIntervalSeconds = 30

    do {
            $awsResponse = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $user -Password $pwd -Query "exec msdb.dbo.rds_task_status @db_name='dbName'" -DisableCommands -AbortOnError
            Write-Host $awsResponse.lifecycle $awsResponse."% complete"
        
            if($awsResponse.lifecycle -eq "SUCCESS") {break}
            start-sleep -seconds $retryIntervalSeconds

    } while ($startDate.AddMinutes($timeOutminutes) -gt (Get-Date))

}

It calls the rds_task_status sproc every X seconds, for a maximum of X minutes, until it gets a lifecycle value of "SUCCESS".

You can of course improve this:

• add a return code or throw in case of timeouts,
• handle the lifecycle value of error, maybe showing the value of task_info which will have the error in it.
• pass in more parameters to make it more generic (like port number of timeouts and retries), etc.

Output looks like this:

Task created successfully.
Task Id: 1
CREATED 0
CREATED 0
CREATED 0
IN_PROGRESS 0
IN_PROGRESS 0
IN_PROGRESS 5
IN_PROGRESS 5
IN_PROGRESS 10
...