Acquia Cloud Drush commands can be confusing when referencing an environment specific database. Luckily, the API infers from your command which environment the database is in.
You might be tempted to add the environment to your database name:
drush @mysitegroup.test ac-database-instance-backup mysitegroupstg
Which will result in an error:
message : Resource not found API status code 404
The 404 occurs because the API cannot find a database name match for "mysitegroupstg."
Instead, use the database name, which is the same across all environments. For example:
drush @mysitegroup.test ac-database-instance-backup mysitegroup