I'm trying to detach and attach a database in AWS RDS. I'm able to detach the database without any issues. But while I try to attach the database, I'm getting few errors.
When I run the attach command for the first time, I get the following error:
Unable to create database: User, group, or role 'sa' already exists in the current database.
When I execute for the second time, I get this:
Msg 1802, Level 16, State 7.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.Msg 5120, Level 16, State 101.
Unable to open the physical file "D:\RDSDBDATA\DATA\DBattachTest.mdf". Operating system error 2: "2(The system cannot find the file specified.)". (Line 6)
Please advise
I'm not sure what you are trying to do but you should be using backup and restore functionality to move DBs around. Attach and detach will not work as they do in a traditional SQL environment: RDS is a managed service and you have extremely limited access to the underlying OS.
From AWS docs:
Restoring a Database
To restore your database, you call the rds_restore_database stored procedure.
The following parameters are required:
@restore_db_name – The name of the database to restore.
@s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.
The following parameters are optional:
@kms_master_key_arn – If you encrypted the backup file, the key to use to decrypt the file.
Example Without Encryption
exec msdb.dbo.rds_restore_database
@restore_db_name='database_name',
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';
Example With Encryption
exec msdb.dbo.rds_restore_database
@restore_db_name='database_name',
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension',
@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id';