Pfthb

I have a database hosted on Amazon RDS , which i need to take backup and create new database out of it. Being new, not sure how to do it. Tried doing from SSMS but it didnt work.

This is one of the top google results but there is some outdated information here.

You can enable sql native backup / restore for sql server in RDS, backup a single database to S3 and then restore that database to the same RDS instance with a new database name.

Steps for environment setup and performing the backup/restore:

1. Create an S3 bucket to store your native backups.


2. Enable Sql native backup by configuring a new option group for your RDS instance and adding the "SQLSERVER_BACKUP_RESTORE" option.


3. Executing a series of procedures in SSMS to run the backup and restore tasks:

exec msdb.dbo.rds_backup_database 
@source_db_name='database_name', @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension', 
@overwrite_S3_backup_file=1;

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';

exec msdb.dbo.rds_task_status @db_name='database_name'

Note: I cannot find any announcement about removing this prior limitation:

You can't restore a backup file to the same DB instance that was used to create the backup file. Instead, restore the backup file to a new DB instance.

However, as of today, I can confirm restoring a native backup to the same instance works as you would expect.

Take a look at the documentation for creating a DB snapshot and restoring from a DB snapshot.

I suppose you use SQL server RDS.
It is not clear if you want to restore database to the same instance or not.
Restoring backup on same instance under different name is not available in Amazon RDS

You can't restore a backup file to the same DB instance that was used to create the >backup file. Instead, restore the backup file to a new DB instance.

Troubleshooting part at

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Using.Poll

Also SQL Server native backup is not supported from SSMS in Amazon RDS since it requires to choose location for backup but in RDS you could not access OS resources.

There are few options:

1) Create another instance from snapshot,

2) if there are many databases and you want to restore only one, you need to enable SQLSERVER_BACKUP_RESTORE and use rds_backup_database to create a backup and rds_restore_database to restore it.

Prerequisite are to have S3 bucket and IAM account has access to S3 bucket
Steps should be:

• change parameter SQLSERVER_BACKUP_RESTORE in option group. Be careful, it might require server reboot.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.BackupRestore.html

• call rds_backup_database in msdb database. Required parameters are database name and S3 bucket, optional are if you want to encrypt backup, overwrite backup with same name in S3 bucket, and back up type full or differential

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Using.Backup

Output of the procedure is task id and it can be used to check status of backup task.

exec msdb..rds_task_status @task_id= 5

• After backup has been created, login to another instance and run rds_restore_database. Parameters are name of the database to restore and S3 bucket where the backup is located.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Using.Restore

If you want you can download backup file from S3 and restore it to SQL server on premise.

3) And the longest one if you want to have both database on same instance, to script database, create under different name and export data to the new database

The whole process is described here

UPDATE: Amazon allowed to restore database on the same instance where backup was created.

https://aws.amazon.com/about-aws/whats-new/2018/10/amazon-rds-for-sql-server-enhances-backup-and-restore-capabilities/