Native Restore Failures on RDS SQL Server

Sign in, start the SQL lab and add the helpers. Wait-Restore reads rds_task_status through .NET so task_info keeps all its lines:

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
.\Start-SqlLab.ps1
$acct = aws sts get-caller-identity --query Account --output text
$bak = "saat-sqlbak-$acct-ap-south-1-an"
function Get-SqlPw([string]$Db) {
  $a = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0].MasterUserSecret.SecretArn" --output text
  (aws secretsmanager get-secret-value --secret-id $a --query SecretString --output text | ConvertFrom-Json).password
}
$csb = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$csb['Data Source'] = '127.0.0.1,11433'; $csb['User ID'] = 'saatadmin'; $csb['Password'] = (Get-SqlPw saat-sql); $csb['Encrypt'] = $true; $csb['TrustServerCertificate'] = $true
function Wait-Restore([string]$Db) {
  do {
    Start-Sleep 15
    $cn = New-Object System.Data.SqlClient.SqlConnection $csb.ConnectionString; $cn.Open()
    $cm = $cn.CreateCommand(); $cm.CommandText = 'exec msdb.dbo.rds_task_status @db_name = @db'; [void]$cm.Parameters.AddWithValue('@db', $Db)
    $t = New-Object System.Data.DataTable; $t.Load($cm.ExecuteReader()); $cn.Close()
    $row = $t.Rows | Sort-Object { [int]$_.task_id } | Select-Object -Last 1
  } until ($row.lifecycle -in 'SUCCESS', 'ERROR', 'CANCELLED')
  "$($row.task_type) $($row.lifecycle)"
  ($row.task_info -replace '\s+', ' ').Trim()
}
function Start-Restore([string]$Key, [string]$Name) {
  sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "exec msdb.dbo.rds_restore_database @restore_db_name='$Name', @s3_arn_to_restore_from='arn:aws:s3:::$bak/$Key';" | Out-Null
  Wait-Restore $Name
}
aws s3 ls "s3://$bak/" | Select-String '\.bak'

You should see something like this:

2026-10-20 10:12:41    3215360 saatshop-full.bak
2026-10-20 10:14:02     118784 saatshop-diff.bak

1Check everything the restore needs

Paste this whole block. Test-NativeRestore.ps1 stays in C:\aws-lab:

$tnr = @'
param([string]$Db = 'saat-sql', [Parameter(Mandatory)][string]$S3Arn)
function Show([bool]$Ok, [string]$Text) { if ($Ok) { "[ OK ] $Text" } else { "[FAIL] $Text" } }
$bucket, $key = ($S3Arn -replace '^arn:aws:s3:::', '') -split '/', 2
$r = aws rds describe-db-instances --db-instance-identifier $Db --query "DBInstances[0]" --output json | ConvertFrom-Json
$region = ($r.DBInstanceArn -split ':')[3]
$og = $r.OptionGroupMemberships[0].OptionGroupName
$roleArn = aws rds describe-option-groups --option-group-name $og --query "OptionGroupsList[0].Options[?OptionName=='SQLSERVER_BACKUP_RESTORE'] | [0].OptionSettings[?Name=='IAM_ROLE_ARN'] | [0].Value" --output text
Show ($roleArn -and $roleArn -ne 'None') "Option group $og has SQLSERVER_BACKUP_RESTORE with role $roleArn"
if (-not $roleArn -or $roleArn -eq 'None') { return }
$roleName = ($roleArn -split '/')[-1]
$trust = aws iam get-role --role-name $roleName --query Role.AssumeRolePolicyDocument --output json | ConvertFrom-Json
$st = @($trust.Statement | Where-Object { $_.Principal.Service -contains 'rds.amazonaws.com' -and $_.Effect -eq 'Allow' })
Show ($st.Count -gt 0) "Role trusts rds.amazonaws.com"
$src = @($st.Condition.StringEquals.'aws:SourceArn' | Where-Object { $_ })
$ogArn = "arn:aws:rds:$($region):$(($r.DBInstanceArn -split ':')[4]):og:$og"
Show (($src.Count -eq 0) -or ($src -contains $r.DBInstanceArn) -or ($src -contains $ogArn)) "Trust condition covers this instance or its option group"
$loc = aws s3api get-bucket-location --bucket $bucket --query LocationConstraint --output text 2>$null
Show ($loc -eq $region) "Bucket $bucket is in $loc (instance is in $region)"
$head = aws s3api head-object --bucket $bucket --key $key --output json 2>$null | ConvertFrom-Json
Show ([bool]$head) "Object $key exists ($([math]::Round($head.ContentLength / 1MB, 1)) MB, encryption $($head.ServerSideEncryption))"
foreach ($t in @(@('s3:GetObject', "arn:aws:s3:::$bucket/$key"), @('s3:ListBucket', "arn:aws:s3:::$bucket"), @('s3:GetBucketLocation', "arn:aws:s3:::$bucket"))) {
  $d = aws iam simulate-principal-policy --policy-source-arn $roleArn --action-names $t[0] --resource-arns $t[1] --query "EvaluationResults[0].EvalDecision" --output text
  Show ($d -eq 'allowed') "Role policy: $($t[0]) is $d"
}
if ($head.ServerSideEncryption -eq 'aws:kms') { "       KMS-encrypted object: the role also needs kms:Decrypt on $($head.SSEKMSKeyId)" }
'@
$tnr | Set-Content C:\aws-lab\Test-NativeRestore.ps1
.\Test-NativeRestore.ps1 -S3Arn "arn:aws:s3:::$bak/saatshop-full.bak"

You should see something like this:

[ OK ] Option group saat-sqlserver-ex-16-og has SQLSERVER_BACKUP_RESTORE with role arn:aws:iam::111122223333:role/saat-rds-backup-role
[ OK ] Role trusts rds.amazonaws.com
[ OK ] Trust condition covers this instance or its option group
[ OK ] Bucket saat-sqlbak-111122223333-ap-south-1-an is in ap-south-1 (instance is in ap-south-1)
[ OK ] Object saatshop-full.bak exists (3.1 MB, encryption AES256)
[ OK ] Role policy: s3:GetObject is allowed
[ OK ] Role policy: s3:ListBucket is allowed
[ OK ] Role policy: s3:GetBucketLocation is allowed

Every link in the chain checks out before you run anything. The simulator tests the role you built in Lesson 46, the one RDS will actually use.

2Restore and read the full task_info

Paste this whole block:

Start-Restore 'saatshop-full.bak' 'SaatShopTest'
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -h -1 -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM SaatShopTest.dbo.Orders"

You should see something like this:

RESTORE_DB SUCCESS
[2026-10-20 10:31:02.557] Task execution has started. [2026-10-20 10:31:08.120] SaatShopTest.bak: Completed processing 100.00% of 3215360 bytes. [2026-10-20 10:31:10.887] Command execution completed successfully.
          3

task_info is a small log, with time stamps for each stage. When a restore fails, the last lines say why.

3Clean the test database

Drop it so the name is available for the breaks:

sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "DROP DATABASE SaatShopTest;"

You should see no output.

✅ Checkpoint: Test-NativeRestore.ps1 shows all OK, the restore of SaatShopTest succeeded with 3 orders, and the full task_info shows each stage.