Sql-server – How to backup Amazon RDS MS SQL Server database instance and restore locally

amazon-rdsamazon-web-servicessql server

It is possible to create a backup of a database running on an Amazon RDS instance and restore it on a local machine using the standard Task -> Backup and Task -> Restore features within Microsoft SQL Server Management Studio? If so, how do you go about doing this?

Note, this question does not pertain to whether you can bulk copy the data or generate the scripts, but whether you can create a true .BAK database backup which can be restored using the SSMS restore feature.

Best Answer

You cant currently create a .bak file out of amazon rds. The way I handle this is to use the azure migration wizard which just happens to also work with amazon rds.

I spin up a EC2 instance with SQL Server and the migration wizard installed. I then use the migration tool to copy the RDS database to the ec2 instance.

once that is done you can create a .bak file from the SQL Server running on the EC2 instance. its a pain but it works. if you have the bandwidth or your database is small you may be able to use the migration tool directly on your target machine.

http://sqlazuremw.codeplex.com

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql-server – How to check if a column exists in a SQL Server table

Related Topic