# Enabling RDS Backup

#### **How to backup and restore Amazon RDS SQL Server**Here are ways to perform a backup and restore:

Follow these steps to create a **snapshot** **via the AWS Management console:**

**Backup**

1. Go to the [Amazon RDS](https://eu-west-1.console.aws.amazon.com/rds/home) page
2. On the left pane click “Snapshots”
3. Click on the “Take snapshot” button
4. Choose “DBInstance” and specify the snapshot’s name
5. Click on the “Take snapshot” button

**Restore**

1. Go to the [Amazon RDS](https://eu-west-1.console.aws.amazon.com/rds/home) page
2. On the left pane click “Snapshots”
3. Select a snapshot you want to restore
4. Click on the “Action” > “Restore snapshot”
5. Fill in the information to create a new RDS\
   \&#xNAN;*If it is required to restore an existing RDS SQL Server instance then first remove it.*<br>
6. Click on the “Restore Snapshot”

### Follow these steps to create a **snapshot** **via Native backup to S3 on the AWS console:**

For the RDS SQL Server instance, it can be performed as a native backup by saving it to S3. The RDS engine does not support the BACKUP DATABASE construct and by executing it, an error message appears. Use the following stored procedure **\[msdb]. \[Dbo]. \[Rds\_backup\_database]** to perform a native backup.

**Backup**

Before the creation of a backup, allow the RDS server to keep backups on S3. This can be set by the following steps:<br>

1. Open [AWS RDS Console](https://eu-west-1.console.aws.amazon.com/rds/home) and go to the Option Groups section<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Option-Groups.png" alt="" width="563"><figcaption></figcaption></figure>
2. Create “Option group”<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Create-group-940x233.png" alt="" width="563"><figcaption></figcaption></figure>
3. Specify the appropriate “Engine Version” and “Engine”<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Create-option-group.png" alt="" width="563"><figcaption></figcaption></figure>
4. Add the option to that group<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Add-option.png" alt="" width="563"><figcaption></figcaption></figure>
5. Specify “SQLSERVER\_BACKUP\_RESTORE” and choose to create a new role in the “IAM role” section. In this case, the role with all the necessary permissions will be created.<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Add-option-IAM-role.png" alt="" width="563"><figcaption></figcaption></figure>
6. Specify a bucket where you prefer to store your backups at the “S3 Destination” section and click on the “Add Option”<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-Set-a-bucket.png" alt="" width="563"><figcaption></figcaption></figure>
7. Now set the “Option Group” option in a database’s settings. To do this choose a database from the list and click “Modify”. In the “Database Option” section, specify the “Option Group” that was created.<br>

   <figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Backup-to-AWS-RDS-Console-set-the-Option-Group.png" alt="" width="563"><figcaption></figcaption></figure>
8. Apply the changes

Now everything is set. As a rule, it takes about fifteen minutes to have all settings applied, even if “apply immediately” was chosen.

Backups and restores are performed via T-SQL commands. The easiest way to do this is to use SQL Server Management Studio.

To perform a backup, please execute the following T-SQL command:

EXEC \[msdb].\[dbo].\[rds\_backup\_database]@source\_db\_name = '\<database name>',@S3\_arn\_to\_backup\_to = N'arn:aws:s3:::\<bucket name>/sample.backup'

If successful, a table in which information about the created backup task will be displayed:

<figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Native-backup-to-S3-Backup-via-SSMS-940x302.png" alt="" width="563"><figcaption></figcaption></figure>

To track the backup process, execute another procedure **EXEC \[msdb]. \[Dbo]. \[Rds\_task\_status]**

<figure><img src="https://blog.sqlbackupandftp.com/wp-content/uploads/2020/04/Native-backup-to-S3-Track-the-backup-process-via-SSMS.png" alt="" width="563"><figcaption></figcaption></figure>

**Restore**

To restore a database, execute the following T-SQL script:

exec msdb.dbo.rds\_restore\_database @restore\_db\_name='\<database name>', @s3\_arn\_to\_restore\_from='arn:aws:s3:::\<bucket name>/sample.backup';

More details about the native backup/restore on S3 can be found [here](https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/).