CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-rds
user3480908
user3480908

Reputation: 111

How to Export/Backup DB from SQL Server on Amazon RDS

I have a SQL Server database on Amazon RDS. How can I export or Backup the database? when I try to I get an error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.

What I'm basically trying to do is to export the database and then import it on to Amazon EC2 on EBS.

Haven't been able to find solution for past 2 or 3 days.

Please help!!!! :)

Upvotes: 5

Views: 12257

Answers (4)

mallocation
mallocation

Reputation: 241

This is not currently supported using Amazon RDS SQL Server. You will have to actually dump the data from the database instance, and cannot produce a .bak file.

I would suggest checking out the SQL Database Migration Wizard. It was built to support SQL -> Azure, but will also allow you to go SQL -> SQL. You will be able to export the data from RDS and import into another database using this tool.

An example of using the tool in this manner. The article is written for importing into RDS, but you can export from RDS in a similar fashion.

Upvotes: 0

Kobi Lehrer
Kobi Lehrer

Reputation: 301

To export data from MS SQL Server on Amazon RDS, you can use either of these choices:

  1. Backup of Native Database using .bak file

Amazon RDS supports native backup for SQL Server databases using the .bak file. Create a full backup of on-premises databases and store it on Amazon S3. Now, restore the backup file onto Amazon RDS DB instance running SQL Server.

  1. SQL Server Import and Export Wizard

Navigate through the Object Explorer to get to the Wizard. When choosing a data source select your RDS SQL Server instance. Use the master user credentials in the Username/Password fields. In destination select SQL Server Native Client 11.0. The wizard can be used for both backups and exports.

Upvotes: 0

Harshit Kumar Pal
Harshit Kumar Pal

Reputation: 1

I had the same issue and AWS has Microsoft SQL Server Native Backup and Restore Support for similar senarios.

Go through the below links you will get the entire details.. https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

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

Upvotes: 0

Denis Stepanenko
Denis Stepanenko

Reputation: 610

As of July, this can be achieved by the following:

  1. Under RDS Dashboard create a new option group with "SQLSERVER_BACKUP_RESTORE" option.

  2. Update your RDS instance to use the newly created option.

  3. Open SQL Management Studio, connect to RDS database and execute the following to kick off the backup:

USE [msdb]
GO

DECLARE   @return_value int

EXEC  @return_value = [dbo].[rds_backup_database]
      @source_db_name = 'your_database_name',
      @S3_arn_to_backup_to = 'arn:aws:s3:::your-bucket-name/folder/db.bak',
      @KMS_master_key_arn = NULL,
      @overwrite_S3_backup_file = NULL

SELECT    'Return Value' = @return_value

GO

To check the progress of the backup, run the following:

> USE [msdb] GO
> 
> DECLARE   @return_value int
> 
> EXEC  @return_value = [dbo].[rds_task_status]         @db_name =
> 'your_database_name',         @task_id = <<<found in result of previous query>>>
> 
> SELECT    'Return Value' = @return_value
> 
> GO

More information here: https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

Upvotes: 12

Related Questions