Backups and DR for RDS SQL Server

Sign in and start the SQL lab:

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
.\Start-SqlLab.ps1

1Set retention and start cross-Region backups

Paste this whole block. Retention goes from 1 to 7 days, the backup window moves to midnight India time, and backups start copying to Singapore:

aws rds modify-db-instance --db-instance-identifier saat-sql --backup-retention-period 7 --preferred-backup-window 18:30-19:00 --apply-immediately --query "DBInstance.DBInstanceStatus" --output text
Start-Sleep 30
aws rds wait db-instance-available --db-instance-identifier saat-sql
$srcArn = aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].DBInstanceArn" --output text
$drKey = aws kms describe-key --key-id alias/aws/rds --region ap-southeast-1 --query KeyMetadata.Arn --output text
aws rds start-db-instance-automated-backups-replication --region ap-southeast-1 --source-region ap-south-1 --source-db-instance-arn $srcArn --kms-key-id $drKey --backup-retention-period 7 --query "DBInstanceAutomatedBackup.Status" --output text
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].[BackupRetentionPeriod,PreferredBackupWindow,LatestRestorableTime]" --output text

You should see something like this:

modifying
pending
7       18:30-19:00     2026-10-20T10:55:00+00:00

18:30 UTC is midnight in India, when SaatShop is quiet. Changing retention between two non-zero values causes no outage. The Singapore copy takes a while to build its first snapshot; you’ll use it in step 4.

2Make the mistake, then restore to just before it

Paste this whole block. It notes the time, deletes almost every order, then restores a copy from before the DELETE (about 30 minutes):

Start-Sleep 60
$t = (Get-Date).ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:ssZ')
Start-Sleep 60
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "DELETE dbo.Orders WHERE OrderId > 100; SELECT COUNT(*) AS left_over FROM dbo.Orders;"
do { Start-Sleep 60; $lrt = aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].LatestRestorableTime" --output text } until ([datetime]$lrt -gt [datetime]$t)
aws rds restore-db-instance-to-point-in-time --source-db-instance-identifier saat-sql --target-db-instance-identifier saat-sql-pitr --restore-time $t --db-instance-class db.t3.micro --db-subnet-group-name saat-db-subnets --vpc-security-group-ids $v.DbSg --no-publicly-accessible --db-parameter-group-name saat-sqlserver-ex-16 --option-group-name saat-sqlserver-ex-16-og --manage-master-user-password --tags Key=course,Value=saatvedha-aws Key=lesson,Value=52 --query "DBInstance.DBInstanceStatus" --output text
aws rds wait db-instance-available --db-instance-identifier saat-sql-pitr
"restored to $t"

You should see something like this:

left_over
-----------
        100
creating
restored to 2026-10-20T11:02:41Z

Production lost 99,903 orders, and saat-sql-pitr is a copy of saat-sql as it was a minute before the DELETE. The restore was given the same subnet group, security group, parameter group and option group, so it’s set up exactly like production.

3Copy the lost rows back

Paste this whole block. It opens a second tunnel to the copy, then streams the missing rows into saat-sql with SqlBulkCopy, keeping their original IDs:

.\Start-SqlLab.ps1 -Db saat-sql-pitr -LocalPort 11435
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
}
function New-Cs([int]$Port, [string]$Db) {
  $b = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
  $b['Data Source'] = "127.0.0.1,$Port"; $b['Initial Catalog'] = 'SaatShop'; $b['User ID'] = 'saatadmin'; $b['Password'] = (Get-SqlPw $Db)
  $b['Encrypt'] = $true; $b['TrustServerCertificate'] = $true
  $b.ConnectionString
}
function Get-Rows([int]$Port) {
  $db = if ($Port -eq 11433) { 'saat-sql' } else { 'saat-sql-pitr' }
  $cn = New-Object System.Data.SqlClient.SqlConnection (New-Cs $Port $db); $cn.Open()
  $cm = $cn.CreateCommand(); $cm.CommandText = 'SELECT COUNT(*) FROM dbo.Orders'; $n = $cm.ExecuteScalar(); $cn.Close(); $n
}
"saat-sql $(Get-Rows 11433)  pitr $(Get-Rows 11435)"
$src = New-Object System.Data.SqlClient.SqlConnection (New-Cs 11435 'saat-sql-pitr'); $src.Open()
$cmd = $src.CreateCommand(); $cmd.CommandText = 'SELECT OrderId, Customer, Amount, CreatedAt FROM dbo.Orders WHERE OrderId > 100'
$rdr = $cmd.ExecuteReader()
$bulk = New-Object System.Data.SqlClient.SqlBulkCopy((New-Cs 11433 'saat-sql'), [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulk.DestinationTableName = 'dbo.Orders'; $bulk.BulkCopyTimeout = 600
foreach ($col in 'OrderId', 'Customer', 'Amount', 'CreatedAt') { [void]$bulk.ColumnMappings.Add($col, $col) }
$bulk.WriteToServer($rdr); $rdr.Close(); $src.Close()
"saat-sql $(Get-Rows 11433)"

You should see something like this:

Tunnel ready on 127.0.0.1,11435 for saatadmin (password is in SQLCMDPASSWORD for this window)
saat-sql 100  pitr 100003
saat-sql 100003

All 99,903 rows are back with their original OrderIds, and nothing written to production after the mistake was lost. This “restore aside, copy back” approach is what DBAs use when only some data is damaged.

4Run a DR restore in Singapore

Paste this whole block. It waits until the Singapore copy can be restored, then restores it (allow up to an hour):

do { Start-Sleep 120; $ab = aws rds describe-db-instance-automated-backups --region ap-southeast-1 --db-instance-identifier saat-sql --query "DBInstanceAutomatedBackups[0].[DBInstanceAutomatedBackupsArn,Status,RestoreWindow.LatestTime]" --output text; $ab } until ($ab -match 'replicating' -and $ab -notmatch 'None$')
$abArn = ($ab -split '\s+')[0]
aws rds restore-db-instance-to-point-in-time --region ap-southeast-1 --source-db-instance-automated-backups-arn $abArn --target-db-instance-identifier saat-sql-dr --use-latest-restorable-time --db-instance-class db.t3.micro --no-publicly-accessible --manage-master-user-password --tags Key=course,Value=saatvedha-aws Key=lesson,Value=52 --query "DBInstance.DBInstanceStatus" --output text
aws rds wait db-instance-available --region ap-southeast-1 --db-instance-identifier saat-sql-dr
aws rds describe-db-instances --region ap-southeast-1 --db-instance-identifier saat-sql-dr --query "DBInstances[0].[DBInstanceStatus,Endpoint.Address,StorageEncrypted]" --output text

You should see something like this:

arn:aws:rds:ap-southeast-1:111122223333:auto-backup:ab-abc123def456  replicating  2026-10-20T11:58:00+00:00
creating
available       saat-sql-dr.c7d8e9f0a1b2.ap-southeast-1.rds.amazonaws.com      True

You now have a working copy of saat-sql in another country, restored from replicated backups, encrypted with Singapore’s key. Its RPO was minutes. In a real DR you’d also need the app, DNS and networking in that Region.

✅ Checkpoint: saat-sql keeps 7 days of backups, saat-sql-pitr was restored from before the DELETE, saat-sql is back to 100,003 orders, and saat-sql-dr is available in ap-southeast-1.