CODEWITHSUNDEEP
sql-serveramazon-rds
feetwet
feetwet

Reputation: 3446

Get local copy of SQL Server hosted on Amazon RDS

I have a small (few hundred MB) SQL Server database running on RDS. I've spent several hours trying to get a copy of it onto my local SQL Server 2014 instance. All of the following fail. Any ideas what might work?

  1. Task -> Backup fails because it doesn't give my admin account permission to backup to a local drive.

  2. Copy Database fails during create package with While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E4D

  3. From SSMS, while connected to the RDS server, running BACKUP DATABASE. This fails with message BACKUP DATABASE permission denied in database 'MyDB'. Even after running EXEC sp_addrolemember 'db_backupoperator' for the connected user.

  4. General scripts generates a 700MB .sql file. Running that with sqlcmd -i fails at some point after producing plausible .mdf and .ldf files that can't be mounted on the local server (probably because the sqlcmd failed to complete and unlock them).

Upvotes: 1

Views: 3118

Answers (4)

feetwet
feetwet

Reputation: 3446

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 stored procedure 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 stored procedure returns 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 SQL Server instance using the SSMS Restore Database... wizard!

Upvotes: 3

André Bonna
André Bonna

Reputation: 807

Check this out: https://github.com/andrebonna/RDSDump

It is a C#.NET Console Application that search for the latest origin database Snapshot, restore it on a temporary RDS instance, generate a BACPAC file, upload it to S3 and delete the temporary RDS instance.

You can transform your RDS snapshot into a BACPAC file, that can be downloaded and imported onto your local SQL Server 2014 instance using the feature answered here (Azure SQL Database Bacpac Local Restore)

Upvotes: 1

onupdatecascade
onupdatecascade

Reputation: 3366

The SSIS Import Export Wizard can generate a package to duplicate a whole set of tables. (It's not the sort of Copy Database function that relies on files - it makes a package with data flow components for each table.)

It's somewhat brittle but can be made to work :-)

SSMS Generate Scripts feature can often fail with any large data set as the script for all the data is just to large/verbose. This method never scripts out the data.

Upvotes: 1

E.J. Brennan
E.J. Brennan

Reputation: 46879

Redgate's SQL Compare and SQL Data Compare are invaluable for these types of things. They are not cheap (but worth every penny imo). But if this is a one-time thing, you could use the 14 day trial and see how it behaves for you.

http://www.red-gate.com/products/

Upvotes: -1

Related Questions