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

Question

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?

"I was trying to copy the entire database to my local db but failing repeatedly." Using what, and how did it fail? — ceejayoz, Mar 18 '15 at 13:17
@ceejayoz: I meant that I tried few scripts, that use backup only. So it didnt work!! — serverstackqns, Mar 18 '15 at 13:33

score 1 · Answer 1 · answered Jan 28 '17 at 18:46

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!

score -1 · Answer 2 · answered May 27 '15 at 21:01

-1

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.

answered May 27 '15 at 21:01

zman

633
6
14

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

2 Answers2