Amazon RDS: Restore snapshot to existing instance

Question

I have created a snapshot of my instance and made some unwanted changes in DB.

Now I want to restore my instance from this snapshot.

When I try to do it - it creates me one more instance, additionally to the one I have.

I specify "DB Instance Identifier" and after that I get two instances with the same ID.

So my question: Is there any way to restore snapshot to existing instance?

Because in other case - new instance is created with differrent endpoint (hostname) and I need to change my configs to access database. Or there is a better way to manage such cases?

Feasible

Just delete your old instance. Problem solved. – Infective

Note to future readers: I suggest checking out jack.chen.job's answer below. It doesn't have as many upvotes as the other answers, but it's much simpler and it seems to have worked for me. – Esse

> Just delete your old instance. Problem solved. not always, all users who use your application with DB deleted will get http 500? Probably, "rename" is a good solution, but anyway: it's awkward .. – Grenier

Answer 1

No you can't restore back your existing DB instance to any of the either manual backup or point-in-time snapshot.

The only way you can make use of the manual backup or automated snapshot is to create a new RDS DB instance using that. Once the new DB instance is created, you can change the endpoint of DB in your app / code and delete the old DB instance.

Bottomline : You have to change the config settings in your app. No other option.

Contemn

Correct, although "now" would be a good time to configure a CNAME in DNS and point it to your RDS hostname, then reconfigure the application to use the cname. Then, in the future, you only need to make one change -- in DNS. – Gassman

Right, I haven't tried it; but i guess that should work. Assuming that would work; The downside of that is the DNS cache and propagation might longer and your app might behave strange. I personally feel that modifying app's config setting is the best to mitigate those kinds of problem. – Contemn

True enough, but if you control the DNS and use sensible TTL, it shouldn't be a deal-breaker. Which approach might be best also depends on how many places exist where the app needs to be reconfigured, or how many different apps might be running against the one database. – Gassman

So, there is no service, like elastic ip for EC2, when you can gust get one static endpoint for your database? This is awkward =) Thanks for answers! – Feasible

Otherwise just copy the database from the restored snapshot instead of changing the endpoint. – Dispel

This is a very good solution, only thing I've done different is: Creating a SQL file from the database that will be created, and use that file to overwrite the data in the old database. Reason for this is, that I search for hour for the solution to edit the database connections in Elastic Beanstalk. It cant be done from the Beanstalk console. For me it was also a quick solution to use Sequel Pro on OSX as MySQL Workbench is incredibly difficult to use. – Headache

I don't think this is correct You have to change the config settings in your app. No other option. When you delete & create an RDS instance in the same region with the same original name, they generally keep the same endpoint. That's what we do with our test RDS instances; delete & recreate from snapshot with the same original name & endpoints don't typically change. – Ake

Answer 2

If anyone came here (just like me) to just restore data without altering your configuration.

Here are the steps :

• Create a new instance(temp) from your automatic snapshots or manually created ones.
• Connect to this instance from either Sequel pro or Mysql workbench.
• Take SQL dump of whatever data you needed from this temp instance.
• Connect your production instance and restore it.
• Now delete the temp instance you created.

Grider

The main benefit of this approach is that you can create a new production instance with the same scripts and configuration as you usually do it, and then copy data into it as the steps suggest. – Schroer

This saved me, but to point out the obvious - If you have lots of data this can end up taking a while! – Worldshaking

Answer 3

Rename original instance and name new instance with original name

https://aws.amazon.com/blogs/aws/endpoint-renaming-for-amazon-rds/

Walton

This link is for changing aws instance name, which will obviously rename your endpoint too. – Sackman

The most useful answer is often at the bottom :( – Campeche

Although the idea seems great, Read Replica's remain attached to the older instance (I've just checked this procedure). I'm guessing, we'll have to reconfigure this newly restored instance for Multi-AZ (if not done during restoration) and create new read replicas too! – Athiste

Answer 4

I had the same issue today. I think you have two options without changing the application's configuration setting.

1. delete the old instance as Mike suggested, then restore it.

2. rename the old instance first ( need to check "Apply immediately" option when rename it).

Biogeography

did u mean "rename the new instance next ( need to check "Apply immediately" option when rename it)." ? – Caniff

No I mean "rename the old instance". Then you can restore the backup to the original name. – Biogeography

Answer 5

As @MaXimus said (I cannot add comments yet) you can:

1. rename original instance first
2. restore from snapshot and assign original instance name

As it's specified in Renaming to Replace an Existing DB Instance here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RenameInstance.html

Jaworski

Answer 6

Solution if you want to run through AWS CLI. Replace ORIG_NAME and NEW_NAME with your values

$ aws rds modify-db-instance \
    --db-instance-identifier ORIG_NAME \
    --new-db-instance-identifier NEW_NAME \
    --apply-immediately

$ aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier NEW_NAME \
    --target-db-instance ORIG_NAME \
    --restore-time 2020-08-27T00:00:00.000Z \
    --publicly-accessible \
    --availability-zone us-east-2a \
    --db-subnet-group-name SUBNET_NAME \
    --vpc-security-group-ids SG_ID

• If you don't have a custom VPC, you can skip --db-subnet-group-name
• If you don't have a custom security group for your RDS, you can skip --vpc-security-group-ids
• If you don't need public access to your instance skip --publicly-accessible

So a short version of the aws rds restore-db-instance-to-point-in-time then would be:

$ aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier NEW_NAME \
    --target-db-instance ORIG_NAME \
    --restore-time 2020-08-27T00:00:00.000Z

Manet

Answer 7

Quite late to answer, but while researching I found a way to avoid any application level changes. As Suggested already there is no option to import snapshot to existing RDS. A probable solution :

One time effort

1. Create a private domain in route 53 [Resolves within your VPC].
2. Use domain/subdoamin to point your RDS instance.
3. Use pointed domain/subdomain in your application.

Everytime you restore a snapshot

1. Create a new RDS instance using snapshot.
2. Point domain/subdomain to new RDS instance.
3. Delete older instance at your convenience.

Heterochromatin

Answer 8

After you created the restored db with a new name you can rename the current instance to ...-old, this also renames the db url. After the url is also changed, you have to rename the restored db to the befored used name, wait until the url of the db changes. Then you have to restart your services, and they will connect to the restored db.

This causes no outage at all.

Slut