SQL Server - database stuck in restoring status

RESTORE DATABASE <DATABASE_NAME> FROM DISK = '<Fuul path>\MyDatabase.bak' WITH REPLACE,RECOVERY

SQL Server - delete backup and restore information

Deletion of backup and restore information from the backup and restore history tables can be done in 2 ways:

1. Deletes backup and restore information about a specified database.

All the information regarding the specific database will be deleted.

No choice to delete between dates.

USE msdb;

GO

exec sp_delete_database_backuphistory @database_name = 'DATABASENAME';

GO

2. Deletes backup and restore information older than a specified date.

Can't be run for one or more specific databases.

Will affect all databases.

USE msdb;

GO  

EXEC sp_delete_backuphistory @oldest_date = '2015-01-01'; 

GO

Remarks:

1. The SPS must be run from msdb database.

2. The SPS affects the following tables:

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

Check the deletion by execute a select on one of them:

SELECT * FROM msdb.dbo.backupset;

3. The physical backup files are preserved.

Restore DB to AWS instance - Error - Aborted the task because of a task failure or a concurrent RESTORE_DB request. Task has been aborted Access Denied

What I tried to do?
Restore SQL Server database backup file (.bak) to AWS RDS instance. (see how to restore here)
After run the restore command, the restoring task was failed with the error message below.
(see error message by running: exec msdb.dbo.rds_task_status @task_id = 8)

Error message:
[2020-02-17 13:06:22.063] Aborted the task because of a task failure or a concurrent RESTORE_DB request. 
[2020-02-17 13:06:22.097] Task has been aborted [2020-02-17 13:06:22.097] Access Denied

Possible solutions:
1. If you don't use Master:
GRANT EXECUTE ON dbo.rds_restore_database to [USERNAME]

2. 1. If you use Master:
Creating an IAM Policy to Access Amazon S3 Resources
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.S3CreatePolicy.html
Creating an IAM Role to Allow Amazon Aurora to Access AWS Services
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.CreateRole.html
Add role to the DB instance
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html

3. Another option: The bak file is not valid or encrypted.
(try to restore other bak and see if it also failed).

Restoring a SQL Server database backup file to AWS RDS instance

What we want to do?
Restore SQL Server database backup file (.bak) to AWS RDS instance.
We have:
1. A bak file
2. An AWS RDS instance.

What we can't do?
We can't use the "restore" task ib SSMS - because we cann't access the local file system of the SQL Server (it handled by AWS).

What we can do?
Restoring using Amazon S3 - the object storage of AWS.

Restoring steps:

In Amazon S3:
1. Create bucket
a. Enter a name.
b. Enter the same Region as in the target RDS instance.
2. Upload the bak file to the bucket.

In AWS RDS:
3. Setting an Option Group
a. Create an option group in AWS RDS --> Options Groups tab.
b. Add SQLSERVER_BACKUP_RESTORE option to the option group.
4. Modify settings in the AWS RDS instance
a. on the databases list, sign your instance and check "Modify" button.
b. Under "Database options" section - select the Option group that was created in step 

In SQL Server Management Studio (SSMS):
5. run msdb.dbo.rds_restore_database (see script below). this SP crates a task to restore the DB
6. check task execution:
exec msdb.dbo.rds_task_status @task_id = 8

(A diagram from aws site)

-- msdb.dbo.rds_restore_database script:

-- ==============================================
-- Declaration
-- ==============================================
DECLARE @par_restore_db_name sysname
DECLARE @par_s3_arn_to_restore_from nvarchar(2048)
DECLARE @par_kms_master_key_arn nvarchar(256)
DECLARE @par_type nvarchar(256)
DECLARE @par_with_norecovery int

----------------------------
-- Set parameter values:
----------------------------
SET @par_restore_db_name = N'ItaiDB'
SET @par_s3_arn_to_restore_from = 'arn:aws:s3:::itaibckt/ItaiDB.bak'
-- @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension'
SET @par_with_norecovery = 0
-- 0 = restore with RECOVERY. The database is online after the restore.
-- 1 = restore with NORECOVERY. The database remains in the RESTORING state after restore task completion.
--SET @par_kms_master_key_arn = 
-- If you encrypted the backup file, the KMS key to use to decrypt the file.
--SET @par_type = 'FULL'
-- Type of restore: DIFFERENTIAL or FULL. The default is FULL

-- ==============================================
-- Execution
-- ==============================================
EXECUTE dbo.rds_restore_database
@restore_db_name = @par_restore_db_name,
@s3_arn_to_restore_from = @par_s3_arn_to_restore_from,
--@kms_master_key_arn = @par_kms_master_key_arn,
--@type = @par_type ,
@with_norecovery = @par_with_norecovery
GO

-- ==============================================
-- check task execution:
-- ==============================================
exec msdb.dbo.rds_task_status @task_id = 8
GO