How to export / archive databases from SQL Server to Amazon RDS

Question

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

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

I mainly try to export a database and then import it into Amazon EC2 on EBS.

Could not find a solution in the last 2 or 3 days.

Please, help!!!!:)

Answer 1

As of July, this can be achieved:

• In the RDS Dashboard, create a new option group with "SQLSERVER_BACKUP_RESTORE".

• Update your RDS instance to use the newly created parameter.

• Open SQL Management Studio, connect to the RDS database, and do the following to start 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, follow these steps:

 > 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/

Answer 2

This is currently not supported using Amazon RDS SQL Server. You will have to actually flush the data from the database instance and you will not be able to create the .bak file.

I would suggest checking out the SQL Database Migration Wizard. It was created to support SQL -> Azure, but will also allow you to switch to SQL -> SQL. Using this tool, you can export data from RDS and import it into another database.

An example of using a tool in this way. The article is written for import into RDS, but you can export from RDS in the same way.

Answer 3

I had the same problem, and AWS had built-in support for Microsoft SQL Server backup and recovery for similar senaria.

By clicking on the links below, you will receive all the information. 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

Answer 4

To export data from MS SQL Server to Amazon RDS, you can use any of these options:

• Backing up your own database using a .bak file

Amazon RDS supports native backup for SQL Server databases using a .bak file. Back up your on-premises databases and save them on Amazon S3. Now restore the backup file to the Amazon RDS DB instance that is running SQL Server.

2. SQL Server Import and Export Wizard

Go to the Object Explorer to go to the Wizard. When choosing a data source, select an instance of RDS SQL Server. Use the credentials of the primary user in the Username / Password fields. In the destination, select SQL Server Native Client 11.0. The wizard can be used for both backup and export.