Amazon RDS - MS SQL DB Export Import

Amazon RDS MSSQL provides native backup and restore as a mechanism to export and import the DB. A full backup file can be created from the on-premise database and stored in Amazon S3. Next this backup file is restored to an existing Amazon RDS DB instance running SQL server. We can follow just the similar steps to export the data from Amazon RDS MSSQL instance and restore it to an on-premise SQL Server database.

The below diagram describes the approach.

mssql_export_import.png

We upload the backup file created above to a pre-decided Amazon S3 bucket in the same region where the target RDS MSSQL database is present. You can follow this link to learn about how to upload.

This native backup and restore mechanism has the following advantages.

  • Migrate databases to Amazon RDS.

  • Move databases between Amazon RDS SQL Server DB instances.

  • Import and export data.

  • Migrate schemas, stored procedures, triggers and other database code.

  • Backup and restore single databases, instead of entire DB instances.

  • Create copies of databases for testing, training, and demonstrations.

  • Store and transfer backup files into and out of Amazon RDS through Amazon S3, giving you an added layer of protection for disaster recovery.

Backing Up a Database

To back up your database, you call the rds_backup_database stored procedure after connecting to the Amazon RDS MSSQL instance from the SSMS client software. The stored procedure is executed using following parameters.

exec msdb.dbo.rds_backup_database 
        @source_db_name='database_name',
        @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
        @kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id',
        @overwrite_S3_backup_file=1,
        @type='FULL';

The successful completion of the above stored procedure creates a backup file and stores it in the mentioned bucket of S3.

Restoring a Database

To restore your database, you call the rds_restore_database stored procedure after connecting to the Amazon RDS MSSQL instance from the SSMS client software. The stored procedure is executed using following parameters.

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';
Advertisements