Item Name: AWS RDS SQL Server Native backup Item Version: v 1.0 Author: Vinod Srinivas
Introduction
A step-by-step guide to schedule a task to perform SQL Server Native Backup of database on AWS RDS and also restore database.
How to set up Native SQL backup?
Pre-requisites
1. AWS account
2. A SQL server instance running on RDS
3. If your database and the backup need to be encrypted, please check this link to ensure if your instance can be encrypted https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html
4. KMS Key to be used for encryption, if encryption is required
5. S3 bucket where the backup files are to be stored
6. MS SQL Server Management Studio, configured to the database instance using Master account
Steps to setup backup
Step 1 – Setup IAM role to have access to the KMS key and the S3 bucket.
1.1 Login to your AWS account
1.2 Launch the IAM console
1.3 Click on Role menu
1.4 Click on Create Role and choose AWS service
1.5 Choose RDS as the service that will use the role
1.6 Select RDS – Add Role to Database as the use case
1.7 Click on Next: Permission button
1.8 Click on Create policy and choose JSON and paste below policy after incorporating your S3 bucket ARN and KMS Key ARN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource": [
"arn:aws:s3:::xxxxxxxxxx"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:*",
"s3:PutObject",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload"
],
"Resource": [
"arn:aws:s3::: xxxxxxxxxx /*"
]
},
{
"Effect": "Allow",
"Action": [
"kms:DescribeKey",
"kms:Encrypt",
"kms:ListKeys",
"kms:GenerateDataKey",
"kms:Decrypt"
],
"Resource": [
"arn:aws:kms:xxxx:xxxxxx:key/yyyy"
]
}
]
}
Step 2 – Setup option group for the database instance.
How to schedule database backup using SQL Server Agent?
Step 1 – Connect to the database.
Step 2 – Create a SQL Server Agent job, give a name to the scheduled job in the General tab.
Step-3 – Add a new step in the Step tab.
Step 4 – Paste the below script in the Command window, replace the highlighted values.
Item Name: AWS RDS SQL Server Native backup
Item Version: v 1.0
Author: Vinod Srinivas
Introduction
A step-by-step guide to schedule a task to perform SQL Server Native Backup of database on AWS RDS and also restore database.
How to set up Native SQL backup?
Pre-requisites
1. AWS account
2. A SQL server instance running on RDS
3. If your database and the backup need to be encrypted, please check this link to ensure if your instance can be encrypted https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html
4. KMS Key to be used for encryption, if encryption is required
5. S3 bucket where the backup files are to be stored
6. MS SQL Server Management Studio, configured to the database instance using Master account
Steps to setup backup
Step 1 – Setup IAM role to have access to the KMS key and the S3 bucket.
1.1 Login to your AWS account
1.2 Launch the IAM console
1.3 Click on Role menu
1.4 Click on Create Role and choose AWS service
1.5 Choose RDS as the service that will use the role
1.6 Select RDS – Add Role to Database as the use case
1.7 Click on Next: Permission button
1.8 Click on Create policy and choose JSON and paste below policy after incorporating your S3 bucket ARN and KMS Key ARN
{"Version": "2012-10-17","Statement": [{"Effect": "Allow","Action": ["s3:ListBucket","s3:GetBucketLocation"],"Resource": ["arn:aws:s3:::xxxxxxxxxx"]},{"Effect": "Allow","Action": ["s3:GetObject","s3:*","s3:PutObject","s3:ListMultipartUploadParts","s3:AbortMultipartUpload"],"Resource": ["arn:aws:s3::: xxxxxxxxxx /*"]},{"Effect": "Allow","Action": ["kms:DescribeKey","kms:Encrypt","kms:ListKeys","kms:GenerateDataKey","kms:Decrypt"],"Resource": ["arn:aws:kms:xxxx:xxxxxx:key/yyyy"]}]}Step 2 – Setup option group for the database instance.
How to schedule database backup using SQL Server Agent?
Step 1 – Connect to the database.
Step 2 – Create a SQL Server Agent job, give a name to the scheduled job in the General tab.
Step-3 – Add a new step in the Step tab.
Step 4 – Paste the below script in the Command window, replace the highlighted values.
BEGIN TRANSACTIONDECLARE @arn varchar(255)select @arn = concat('arn:aws:s3:::xxxxxxxxxx/dbname_' ,REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak');EXEC msdb.dbo.rds_backup_database@source_db_name = 'dbname',@S3_arn_to_backup_to = @arn,@KMS_master_key_arn = 'arn:aws:kms:region:accountid:key/keyarn',@overwrite_S3_backup_file = 1COMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GOStep 5 – Add a schedule in the Schedule tab.
Step 6 – Setup notification in Notification tab as required.
How to restore a database using Native SQL backup?
Step 1 – Connect to the database.
Step 2 – Identify the backup file from the S3 bucket using which the database is to be restored.
Step 3 – Restore database from backup.
Database name to restore backup should not exist on the instance.
Run below query to start restore using a backup file in S3.
exec msdb.dbo.rds_restore_database@restore_db_name='database_name',@s3_arn_to_restore_from='arn:aws:s3::: bucket_name/file_name_and_extension',@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id';Check status of the restore by running below query.
exec mscdb.dbo.rds_task_status @db_name=’database_name';Recent Posts
Cost Saving: $25,000* Year-over-Year for Our Client
October 3, 2023Comparative Analysis of Integration Engine
October 3, 2023Selecting the Perfect Interface Engine for Your
September 14, 2023Synchronization And The Atomic Integer
August 14, 2023ERP Solutions for Contract Lifecycle Management
August 1, 2023Recent Comments
Popular Categories
Archives