How to backup and restore Amazon RDS SQL Server

Amazon RDS SQL Server is a cloud database from Amazon. The way RDS SQL Server performs backups is the main difference between this and 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:

  1. Export bacpac\dacpac
  2. Snapshot creation
  3. Native backup to S3
  4. Via SqlBak

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. It’s necessary to admit that 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 a recommendation for consistency. There can be the following: During the creation of a backup, some changes are added into tables. This means that there will not be 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:

  1. Run SSMS and set the connection to Amazon RDS SQL Server
  2. Go to the “Object Explorer” section, right-click on the required database and select “Tasks” > “Export Data-tier Application”
  3. Sеt a path where the backups should be stored, then click “Next” > “Finish”
  4. If the process completed successfully, the following window will be displayed:
  5. 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:

  1. Run SSMS and set the connection to Amazon RDS SQL Server
  2. Go to the “Object Explorer” section, right-click on the required database and select “Import Data tier-Application”
  3. Specify the path to the bacpac file
  4. Specify a database’s name. “Data file path” and “Log file path” values leave by default
  5. If everything is successful, the following will be displayed:
  6. 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 perform Import/Еxport SQL databases. Find more details about that tool and a download link at Microsoft’s official web site.

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

That’s it!

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. Note, that 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
      1. Open Amazon RDS page
      2. Click “Snapshots” on the left pane
      3. Press on the “Take snapshot” button
      4. Select “DBInstance” and enter the snapshot’s name
      5. Press on the “Take snapshot” button
Restore
      1. Open the Amazon RDS page
      2. Click “Snapshots” on the left pane
      3. Choose a snapshot you want to restore
      4. Press on the “Action” > “Restore snapshot”
      5. Specify the information to create a new RDS
        To restore an existing RDS SQL Server instance, please remove it beforehand.
      6. Press on the “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:

      1. Go to AWS RDS Console and click on the Option groups menu.
      2. Create “Option group”
      3. Specify the “Engine Version” and “Engine”.
      4. Add the option to that group by clicking the “Add option” button.
      5. 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.
      6. At the “S3 Destination” section, specify a bucket where you prefer to store your backups and click on the “Add Option” button.
      7. Now set the “Option Group” option in a database’s settings. To do this, choose the database you need and press “Modify”. At the “Database options” section, choose the “Option Group” that was created.
      8. Apply all the changes

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

SqlBak is an application to perform scheduled backups and restore the backups of your DBMS, in particular, it supports RDS 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, scheduled 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
  1. Once the SqlBak app is installed, set a connection to the RDS server.
  2. Create a backup job. To do this, please go to the Dashboard and click on the “Add new job” button.
  3. Choose the databases that should be backed up.
  4. Select a destination place where the backups should be stored. Note, multiple destinations can be selected.
  5. 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
  1. To restore the latest backup, go to your “Dashboard” page and click on the “Restore” link in the “LAST RUN” column.
  2. Select a database you need to restore and click on the “Restore selected” button.
  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *