AWS Importing and Exporting SQL Server Databases

readme.md

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Backing Up a Database

To back up your database, you call the rds_backup_database stored procedure.

Note You can't back up a database during the maintenance window, or any time Amazon RDS is in the process of taking a snapshot of the database. The following parameters are required:

• @source_db_name – The name of the database to create a backup of.
• @s3_arn_to_backup_to – The Amazon S3 bucket to save the backup file in, and the name of the file. The file can have the extension .bak, or any extension you want.

The following parameters are optional:

• @kms_master_key_arn – If you want to encrypt the backup file, the key to use to encrypt the file. For more information about encrytion keys, see Getting Started in the AWS Key Management Service (AWS KMS) documentation.
• @overwrite_S3_backup_file – Whether or not to overwrite the backup file if it already exists in the Amazon S3 bucket. Specify 1 to overwrite the existing file. This overwrites any file in the bucket with the specified name, whether it is a backup file or another type of file. Specify 0 to not overwrite the existing file, and return an error instead if the file already exists. The default is 0. Example Without Encryption

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',
        @overwrite_S3_backup_file=1;

Example with Encryption

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;

Restoring a Database

To restore your database, you call the rds_restore_database stored procedure.

The following parameters are required:

• @restore_db_name – The name of the database to restore.
• @s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.

The following parameters are optional:

• @kms_master_key_arn – If you encrypted the backup file, the key to use to decrypt the file. Example Without Encryption

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

Example with Encryption

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

Canceling a Task

To cancel a backup or restore task, you call the rds_cancel_task stored procedure.

The following parameters are optional:

• @db_name – The name of the database to cancel the task for.
• @task_id – The ID of the task to cancel. You can get the task ID by calling rds_task_status.

Example

exec msdb.dbo.rds_cancel_task @task_id=1234;

Tracking the Status of Tasks

To track the status of your backup and restore tasks, you call the rds_task_status stored procedure. If you don't provide any parameters, the stored procedure returns the status of all tasks. The status for tasks is updated approximately every 2 minutes.

The following parameters are optional:

• @db_name – The name of the database to show the task status for.
• @task_id – The ID of the task to show the task status for.

Example

exec msdb.dbo.rds_task_status @db_name='database_name'

The rds_task_status stored procedure returns the following columns.

Column	Description
task_id	The ID of the task.
task_type	Either BACKUP_DB for a back up task, or RESTORE_DB for a restore task.
database_name	The name of the database that the task is associated with.
% complete	The progress of the task as a percentage.
duration (mins)	The amount of time spent on the task, in minutes.
lifecycle	The status of the task. The possible statuses for a task are the following: - CREATED – As soon as you call rds_backup_database or rds_restore_database, a task is created and the status is set to CREATED. - IN_PROGRESS – After a backup or restore task starts, the status is set to IN_PROGRESS. It can take up to 5 minutes for the status to change from CREATED to IN_PROGRESS. - SUCCESS – After a backup or restore task completes, the status is set to SUCCESS. - ERROR – If a backup or restore task fails, the status is set to ERROR. Read the task_info column for more information about the error. - CANCEL_REQUESTED – As soon as you call rds_cancel_task, the status of the task is set to CANCEL_REQUESTED. - CANCELLED – After a task is successfully canceled, the status of the task is set to CANCELLED.
task_info	Additional information about the task. If an error occurs while backing up or restoring a database, this column contains information about the error.
last_updated	The date and time that the task status was last updated. The status is updated after every 5% of progress.
created_at	The date and time that the task was created.
overwrite_S3_backup_file	The value of the @overwrite_S3_backup_file parameter specified when calling a backup task. For more information, see Backing Up a Database.

On backup there is an overwrite S3 option. What about on restore?

"Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location."

Where is the WITH REPLACE option for msdb.dbo.rds_restore_database?

Is there an easy way to handle this case?

My RDS instance is composed of a number of databases (each database has exactly the same structure but for business reasons, we prefer to have a database for every customer). What I'm trying to achieve is backup every single database into a different file so that if any customer needs to restore a backup we don't have to restore the whole RDS instance but just the database needed.
At the moment we are using a cron job, but we would like to get rid of it.
Reading the documentation it looks like if you can only backup a whole instance, do you have any suggestion on how to handle this scenario.
Many thanks (please let me know if the question is not clear enough)