Native Backup and Restore with S3

Sign in, start the SQL lab and set names:

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"

1Create the bucket and the backup role

Paste this whole block:

aws s3api create-bucket --bucket $bak --bucket-namespace account-regional --region ap-south-1 --create-bucket-configuration LocationConstraint=ap-south-1 | Out-Null
aws s3api put-bucket-tagging --bucket $bak --tagging 'TagSet=[{Key=course,Value=saatvedha-aws},{Key=lesson,Value=46}]'
$trust = @{ Version = '2012-10-17'; Statement = @(@{ Effect = 'Allow'; Principal = @{ Service = 'rds.amazonaws.com' }; Action = 'sts:AssumeRole'; Condition = @{ StringEquals = @{ 'aws:SourceAccount' = $acct; 'aws:SourceArn' = @("arn:aws:rds:ap-south-1:$($acct):db:saat-sql", "arn:aws:rds:ap-south-1:$($acct):og:saat-sqlserver-ex-16-og") } } }) }
$trust | ConvertTo-Json -Depth 6 | Set-Content rds-backup-trust.json
$perm = @{ Version = '2012-10-17'; Statement = @(
  @{ Effect = 'Allow'; Action = @('s3:ListBucket', 's3:GetBucketLocation'); Resource = "arn:aws:s3:::$bak" },
  @{ Effect = 'Allow'; Action = @('s3:GetObjectAttributes', 's3:GetObject', 's3:PutObject', 's3:ListMultipartUploadParts', 's3:AbortMultipartUpload'); Resource = "arn:aws:s3:::$bak/*" }) }
$perm | ConvertTo-Json -Depth 6 | Set-Content rds-backup-s3.json
$roleArn = aws iam create-role --role-name saat-rds-backup-role --assume-role-policy-document file://rds-backup-trust.json --query Role.Arn --output text
aws iam put-role-policy --role-name saat-rds-backup-role --policy-name saat-sqlbak-s3 --policy-document file://rds-backup-s3.json
$roleArn

You should see something like this:

arn:aws:iam::111122223333:role/saat-rds-backup-role

The role can only be used by saat-sql and its option group, and only on this one bucket. That’s tighter than the console’s auto-created role, which is the point.

2Add the backup option to saat-sql

Paste this whole block. The option group change applies in a few minutes:

Start-Sleep 15
aws rds add-option-to-option-group --option-group-name saat-sqlserver-ex-16-og --options "OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=$roleArn}]" --apply-immediately --query "OptionGroup.Options[].OptionName" --output text
Start-Sleep 30
aws rds wait db-instance-available --db-instance-identifier saat-sql
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].OptionGroupMemberships[0].[OptionGroupName,Status]" --output text

You should see something like this:

SQLSERVER_BACKUP_RESTORE
saat-sqlserver-ex-16-og in-sync

Adding this option doesn’t need a reboot. It creates the rds_ procedures in msdb that the next steps use.

3Back up SaatShop: full, then differential

Paste this whole block. It adds a status helper, takes a full backup, adds an order, then takes a differential:

function Wait-RdsTask([string]$Db) {
  do {
    Start-Sleep 15
    $rows = sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -h -1 -W -s "|" -Q "SET NOCOUNT ON; exec msdb.dbo.rds_task_status @db_name='$Db'" | Where-Object { $_ -match '\|' }
    $f = ($rows | Sort-Object { [int](($_ -split '\|')[0]) } | Select-Object -Last 1) -split '\|'
  } until ($f[5] -in 'SUCCESS', 'ERROR', 'CANCELLED')
  "$($f[1]) $($f[2]) $($f[3])% $($f[5]) $($f[6])"
}
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "exec msdb.dbo.rds_backup_database @source_db_name='SaatShop', @s3_arn_to_backup_to='arn:aws:s3:::$bak/saatshop-full.bak', @overwrite_s3_backup_file=1, @type='FULL';" | Out-Null
Wait-RdsTask SaatShop
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "INSERT dbo.Orders (Customer, Amount) VALUES (N'Kiran', 999.00);"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "exec msdb.dbo.rds_backup_database @source_db_name='SaatShop', @s3_arn_to_backup_to='arn:aws:s3:::$bak/saatshop-diff.bak', @overwrite_s3_backup_file=1, @type='DIFFERENTIAL';" | Out-Null
Wait-RdsTask SaatShop
aws s3 ls "s3://$bak/"

You should see something like this:

BACKUP_DB SaatShop 100% SUCCESS Task execution has completed successfully.

(1 rows affected)
BACKUP_DB_DIFFERENTIAL SaatShop 100% SUCCESS Task execution has completed successfully.
2026-10-20 10:12:41    3215360 saatshop-full.bak
2026-10-20 10:14:02     118784 saatshop-diff.bak

The full backup has everything; the differential has only what changed since, which is why it’s so small. In real work you might take a full on Sunday and differentials each night before a migration weekend.

4Restore the chain as a new database

Paste this whole block:

sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "exec msdb.dbo.rds_restore_database @restore_db_name='SaatShopCopy', @s3_arn_to_restore_from='arn:aws:s3:::$bak/saatshop-full.bak', @with_norecovery=1, @type='FULL';" | Out-Null
Wait-RdsTask SaatShopCopy
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "SELECT name, state_desc FROM sys.databases WHERE name = 'SaatShopCopy'"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "exec msdb.dbo.rds_restore_database @restore_db_name='SaatShopCopy', @s3_arn_to_restore_from='arn:aws:s3:::$bak/saatshop-diff.bak', @with_norecovery=0, @type='DIFFERENTIAL';" | Out-Null
Wait-RdsTask SaatShopCopy
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "SELECT COUNT(*) AS orders FROM SaatShopCopy.dbo.Orders"

You should see something like this:

RESTORE_DB SaatShopCopy 100% SUCCESS Task execution has completed successfully.
name          state_desc
------------- ----------
SaatShopCopy  RESTORING
RESTORE_DB_DIFFERENTIAL SaatShopCopy 100% SUCCESS Task execution has completed successfully.
orders
------
     4

After the full restore the copy waits in RESTORING, ready for more. The differential brought in Kiran’s order and opened the database. This is exactly how you move an on-premises database with a short outage: full backup days before, differential during the cutover.

Want the file on your laptop? aws s3 cp "s3://$bak/saatshop-full.bak" . downloads it, and any SQL Server 2022 or later can restore it.

✅ Checkpoint: rds_task_status shows SUCCESS for both backups and both restores, the bucket holds saatshop-full.bak and saatshop-diff.bak, and SaatShopCopy has 4 orders.