Amazon RDS SQL Server is a cloud database from Amazon. The way RDS SQL Server performs backups is the main difference between this and the classic SQL Server. It offers the following: snapshots, restore point-in-time, Export Data-tier Application and native backup to S3 (instead of native T-SQL BACKUP DATABASE construction).
Here are ways to perform a backup and restore:
Export bacpac/dacpac
Exporting a database is one of the ways to backup the Amazon RDS server. Using this method, users receive the database directly to the local machine. However, there is one significant drawback: the transactional consistency of the exported data isn’t guaranteed by default. For the duration of the backup, the database should be put in a read-only mode to receive a transactionally consistent backup.
Via SSMS (SQL Server Management Studio)
Backup
The database should be in read-only mode to perform a Bacpac export via SSMS. This is recommended for consistency. Otherwise, issues could occur such as a user adding changes to tables during backup creation. This would mean that there is not a snapshot of the entire database at a point in time. If you need the data to be exactly as it is at the time of the start of the backup, the database must be converted to read-only mode. Run the following T-SQL query to set a read-only mode for a database:
ALTER DATABASE [<dbname>] SET READ_ONLY WITH ROLLBACK AFTER 5
Follow these instructions to export a database:
- Run SSMS and set the connection to Amazon RDS SQL Server
- Go to the “Object Explorer” section, right-click on the required database and select “Tasks” > “Export Data-tier Application”
- Sеt a path where the backups should be stored, then click “Next” > “Finish”
- If the process completed successfully, the following window will be displayed:
- Execute the following command to set the database to read-write mode:
ALTER DATABASE [<dbname>] SET READ_WRITE WITH ROLLBACK AFTER 5
Restore
Via the next steps, a database’s import can be performed:
- Run SSMS and set the connection to Amazon RDS SQL Server
- Go to the “Object Explorer” section, right-click on the required database and select “Import Data tier-Application”
- Specify the path to the bacpac file
- Specify a database’s name. “Data file path” and “Log file path” should stay at their default values.
- If everything is successful, the following will be displayed:
- Execute the following T-SQL command to put the database into read-write mode:
ALTER DATABASE [<dbName>] SET READ_WRITE WITH ROLLBACK AFTER 5
Via SqlPackage
Use a small utility from Microsoft, SqlPackage.exe, to import/export SQL databases. Find more details about that tool and a download link at Microsoft’s official website.
Via that tool, several types of export can be performed. More suitable for Amazon RDS Server is the Extract dacpac with ExtractAllTableData = True option.
Backup
Before submitting it, it is highly recommended to set the target database to read-only mode and switch it back to read-write mode once the backup is made. To see how to do this, refer to the section above.
To export, run the following command, where you specify the database and all required information for the connection:
sqlpackage.exe /action:Extract /SourceServerName:<RDS Server name> /SourceDatabaseName:<db name> /SourceUser:admin /SourcePassword:<password> /TargetFile:<patch to target .dacpac> /p:ExtractAllTableData=True
Restore
The restore process can be run via the following command:
sqlpackage.exe /action:Publish /TargetServerName:<RDS Server name> /TargetDatabaseName:<db name> /TargetUser:admin /TargetPassword:<password> /SourceFile:<patch to source .dacpac> /p:TreatVerificationErrorsAsWarnings=True
Snapshot creation
Instead of classic backups, Amazon Web Services provides functionality for creating database snapshots. Snapshot creation is a good way to create hot backups, but it has two significant drawbacks. First, there is no way to perform a snapshot for a specific database, it is performed for the entire SQL Server instance. The second is that the backup file cannot be received, because it is stored on RDS servers. Please note that the file can be restored only on the RDS server.
Via AWS Management Console
Via the following simple steps, a snapshot can be performed via AWS Management console:
Backup
-
-
- Open Amazon RDS page
- Click “Snapshots” on the left pane
- Press on the “Take snapshot” button
- Select “DBInstance” and enter the snapshot’s name
- Press on the “Take snapshot” button
-
Restore
-
-
- Open the Amazon RDS page
- Click “Snapshots” on the left pane
- Choose a snapshot you want to restore
- Press on “Action” > “Restore snapshot”
- Specify the information to create a new RDS
To restore an existing RDS SQL Server instance, please remove it beforehand. - Press on “Restore Snapshot”
-
Via AWS CLI
Use the AWS CLI utility to create a snapshot via the command prompt. Note, a user configured in AWS CLI must be included in the AmazonRDSFullAccess group.
Backup
Execute the following to create a snapshot:
aws rds create-db-snapshot --db-instance-identifier <DB identifier> --db-snapshot-identifier <snapshot name>
Something like this should be received in response:
{
"DBSnapshot": {
"DBSnapshotIdentifier": "backup-5-3-2020",
"DBInstanceIdentifier": "database-1",
"Engine": "sqlserver-ex",
"AllocatedStorage": 20,
"Status": "creating",
"Port": 1433,
"AvailabilityZone": "us-east-1d",
"VpcId": "vpc-03102ec7978f7ff8b",
"InstanceCreateTime": "2020-03-20T11:54:50.858Z",
"MasterUsername": "admin",
"EngineVersion": "14.00.3223.3.v1",
"LicenseModel": "license-included",
"SnapshotType": "manual",
"OptionGroupName": "default:sqlserver-ex-14-00",
"PercentProgress": 0,
"StorageType": "gp2",
"Encrypted": false,
"DBSnapshotArn": "arn:aws:rds:us-east-1:552663163906:snapshot:backup-5-3-2020",
"IAMDatabaseAuthenticationEnabled": false,
"ProcessorFeatures": [],
"DbiResourceId": "db-UO6HQQ5ONIRHPHU72QXHP2CBJE"
}
}
Restore
Execute the following command to restore a database from a snapshot:
aws rds restore-db-instance-from-db-snapshot --db-instance-identifier <DB identifier> --db-snapshot-identifier <snapshot name> --db-subnet-group-name <subnet group name>
Note the –-db-subnet-group-name parameter should be specified, the required values can be found by running aws rds describe-db-subnet-groups
Something like this should be received in response:
{
"DBInstance": {
"DBInstanceIdentifier": "database-5",
"DBInstanceClass": "db.t2.micro",
"Engine": "sqlserver-ex",
"DBInstanceStatus": "creating",
"MasterUsername": "admin",
"AllocatedStorage": 20,
"PreferredBackupWindow": "07:52-08:22",
"BackupRetentionPeriod": 7,
"DBSecurityGroups": [],
"VpcSecurityGroups": [
{
"VpcSecurityGroupId": "sg-030ac53ed295429fb",
"Status": "active"
}
],
"DBParameterGroups": [
{
"DBParameterGroupName": "default.sqlserver-ex-14.0",
"ParameterApplyStatus": "in-sync"
}
],
"DBSubnetGroup": {
"DBSubnetGroupName": "default-vpc-03102ec7978f7ff8b",
"DBSubnetGroupDescription": "Created from the RDS Management Console",
"VpcId": "vpc-03102ec7978f7ff8b",
"SubnetGroupStatus": "Complete",
"Subnets": [
{
"SubnetIdentifier": "subnet-03bbe145f518226cb",
"SubnetAvailabilityZone": {
"Name": "us-east-1c"
},
"SubnetStatus": "Active"
},
{
"SubnetIdentifier": "subnet-0ce37a12925bd5864",
"SubnetAvailabilityZone": {
"Name": "us-east-1e"
},
"SubnetStatus": "Active"
},
{
"SubnetIdentifier": "subnet-0191b34946572bb0f",
"SubnetAvailabilityZone": {
"Name": "us-east-1b"
},
"SubnetStatus": "Active"
},
{
"SubnetIdentifier": "subnet-0ffd5cb199b27daa6",
"SubnetAvailabilityZone": {
"Name": "us-east-1a"
},
"SubnetStatus": "Active"
},
{
"SubnetIdentifier": "subnet-03a47697122e0b4f7",
"SubnetAvailabilityZone": {
"Name": "us-east-1f"
},
"SubnetStatus": "Active"
},
{
"SubnetIdentifier": "subnet-015e10c2ae3e9a2bd",
"SubnetAvailabilityZone": {
"Name": "us-east-1d"
},
"SubnetStatus": "Active"
}
]
},
"PreferredMaintenanceWindow": "fri:03:12-fri:03:42",
"PendingModifiedValues": {},
"MultiAZ": false,
"EngineVersion": "14.00.3223.3.v1",
"AutoMinorVersionUpgrade": false,
"ReadReplicaDBInstanceIdentifiers": [],
"LicenseModel": "license-included",
"OptionGroupMemberships": [
{
"OptionGroupName": "default:sqlserver-ex-14-00",
"Status": "pending-apply"
}
],
"CharacterSetName": "SQL_Latin1_General_CP1_CI_AS",
"PubliclyAccessible": false,
"StorageType": "gp2",
"DbInstancePort": 0,
"StorageEncrypted": false,
"DbiResourceId": "db-MKXHALKYXHU3M44UPZA3ABEFSQ",
"CACertificateIdentifier": "rds-ca-2019",
"DomainMemberships": [],
"CopyTagsToSnapshot": false,
"MonitoringInterval": 0,
"DBInstanceArn": "arn:aws:rds:us-east-1:552663163906:db:database-5",
"IAMDatabaseAuthenticationEnabled": false,
"PerformanceInsightsEnabled": false,
"DeletionProtection": false,
"AssociatedRoles": []
}
}
Native backup to S3
A native backup by saving to S3 for an Amazon RDS SQL Server instance can be performed. An error message appears if using Standard BACKUP DATABASE statement because the RDS engine doesn’t support it. To perform a native backup, use the following stored procedure: [msdb]. [Dbo]. [Rds_backup_database].
Backup
It is necessary to allow the Amazon RDS server to keep backups on S3 before the creation of a backup. Use the following steps to set it:
-
-
- Go to AWS RDS Console and click on the Option groups menu.
- Create “Option group”
- Specify the “Engine Version” and “Engine.”
- Add the option to that group by clicking the “Add option” button.
- Choose “SQLSERVER_BACKUP_RESTORE” and create a new role in the “IAM role” section. The role with all the required permissions will be created in this case.
- At the “S3 Destination” section, specify a bucket where you prefer to store your backups and click on the “Add Option” button.
- Now set the “Option Group” option in a database’s settings. To do this, choose the database you need and press “Modify.” In the “Database options” section, choose the “Option Group” that was created.
- Apply all the changes
- Go to AWS RDS Console and click on the Option groups menu.
-
Now all the settings are made. As a rule, about fifteen minutes is needed to have all settings applied, even if the “apply immediately” option was used.
Backup and restore processes are performed via T-SQL commands. Using SQL Server Management Studio is the easiest and the best way.
Please execute the following T-SQL command to perform a backup:
EXEC [msdb].[dbo].[rds_backup_database] @source_db_name = '<database name>', @S3_arn_to_backup_to = N'arn:aws:s3:::<bucket name>/sample.backup'
The information about the backup creation will be displayed if everything works smoothly:
Run another procedure EXEC [msdb]. [Dbo]. [Rds_task_status] to track the backup process.
Restore
Execute the following T-SQL script to restore a database:
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.
Via SqlBak
Use SqlBak to backup and restore Amazon RDS SQL Server databases. With SqlBak you can send backups to your local drive or somewhere in the cloud. Along with these options, SqlBak can send email notifications, compress backups, schedule backups and much more.
As described in the previous methods, for transactional consistency you have to set the database to read-only mode for the duration of the backup. To do this, please check off the “Make database read-only (for export to be transactionally consistent)” option in the “Job options” section.
Backup
- Once the SqlBak app is installed, set a connection to the RDS server.
- Create a backup job. To do this, please go to the Dashboard and click on the “Add new job” button.
- Choose the databases that should be backed up.
- Select a destination place where the backups should be stored. Note, multiple destinations can be selected.
- The simple backup job is ready, to run a backup manually, click save the setting and then click on the “Run now” button. If everything is OK, the backup process will start.
Restore
- To restore the latest backup, go to your “Dashboard” page and click on the “Restore” link in the “LAST RUN” column.
- Select a database you need to restore and click on the “Restore selected” button.
- On the next form, enter a name for the database to be restored and click the “Restore” button to continue. The database will be overwritten if it exists.
