Did you know that you can restore AWS RDS databases to a SQL Server 2022 instance running locally using native backup and restore? Well you can. Let’s talk about it.
Why S3?
So, why put backups on S3 at all? Two big reasons.
Let’s say you’re mostly, or strictly, on-premises with your servers. You can use S3 storage on AWS as a way to get your backups offsite. I just recently tried to help someone in the forums who was taking backups, but not testing them in any way, only to find that their local storage had become corrupted and when they needed the backup, it wasn’t there. First, of course, they should be testing their backups. However, in addition, they should have moved the backup to an offsite location. S3 storage is a simple & quick way to get that done.
Now, let’s reverse this and say that you’re mostly, or strictly, running AWS RDS SQL Server instances. You can backup those instances to S3 storage. You could then, copy that down locally if you want to create an offsite storage from AWS for your databases. And, being paranoid, that sounds like a great idea to me. I have a high degree of assurance that my data is protected in AWS, but another way to recover safely from an outage, well, that’s a good thing to have.
Copying files around is all well & good, but, I’m lazy. Can’t I just run a restore, direct from AWS? Well, you can in SQL Server 2022.
Restore AWS RDS Databases
You can’t simply point your server at S3 storage. There are quite a few steps to get this done. I’m not going to drill down on each of these, however, we’ll run through ’em all, just so you have a good idea what kind of work (and learning) is in front of you.
- You’ve got an AWS account. Hard to do anything without the foundation.
- You’ve got an AWS RDS database.
- You’ve got an S3 storage defined.
- It’s in the same region as your AWS RDS database.
- Create an IAM user
- For that user, create a public and private key, note both down
- Give that IAM user access to your S3 storage
That’s the basic setup on AWS. Then, in order to restore AWS RDS database to a local instance, we have to do some stuff, at that local instance:
- Create a credential using the S3 secret key
Oh wait, that’s it. Seriously?
Yes. From there, it’s just a question of using the URL from the S3 storage:
RESTORE DATABASE adw
FROM URL = 's3://dbbackups-scary.s3.us-east-1.amazonaws.com/adw.bak'And you can backup there directly as well.
Conclusion
Yeah, there’s quite a bit of work setting up your AWS S3 storage so that you’ve got the security settings correct. But that’s the principal part of the work. After that, it’s just a question of creating the local credentials and then you’re done.
In short, there’s no excuse for getting your databases offsite, or, conversely, testing your AWS RDS databases locally. You’ve got options now.