I restored a database from our datacentre backup uploaded to S3 bucket, which had CDC enabled. During the restore it failed because the sa user did not exists on the RDS instance (admin user has a different name).
I was able to make the new admin the owner but whenever I run exec msdb.dbo.rds_cdc_disable_db 'database_name' I get the following error.
Msg 3903, Level 16, State 1, Procedure msdb.dbo.rds_cdc_disable_db, Line 63 [Batch Start Line 0]
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 50000, Level 16, State 1, Procedure msdb.dbo.rds_cdc_disable_db, Line 70 [Batch Start Line 0]
Could not update the metadata that indicates database database_name is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.
Not sure how to disable CDC so I can re-enable it so the jobs get created.