Home / server / Questions / 676411
In Process
serverstackqns
Asked: 2015-03-19 05:08:16 +0800 CST

Clone database or copy data from RDS MSSQL DB to local database

  • 772

Is there anyway I can clone an amazon RDS MSSQL DB to my ec2 instance local DB? And this has to be done through powershell also.

Basically I need backup of RDS MSSQL DB in sql or bak extension, but that's not achievable in a direct attempt. I was trying to copy the entire database to my local db (ec-2 instance), but failing repeatedly.

Happy to support if you need anymore info.

Can someone please help me on this? Any support would be really appreciated!!

Edit:

Have tried using SQLAzureMW and tables and schemas found to have copied, but not users or roles. Any other alternative?

amazon-ec2

2 Answers

  1. AWS has finally provided a reasonably easy means of doing this: It requires an S3 bucket.

    After creating a bucket called rds-bak I ran the following sproc in the RDS instance:

    exec msdb.dbo.rds_backup_database
   @source_db_name='MyDatabase',
   @s3_arn_to_backup_to='arn:aws:s3:::rds-bak/MyDatabase.bak',
   @overwrite_S3_backup_file=1;

    The following sproc gives the status of the backup request:

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

    Once it finished I downloaded the .bak file from S3 and imported it into a local MS SQL instance using the SSMS Restore Database... wizard!

    • 1

  2. You can use SQL MGMT studio to connect to AWS RDS then if you may be able to backup you database if not you should be able to export it via script.

    • -1