… is to use a SQL Server where a robot does it for you!
Let’s take a look at how you can do this in AmazonRDS’s hosted SQL Server these days.
Normally, restoring transaction logs is super tedious.
You’ve got to restore all the files in the right order, for every single database. Even if you script it, if you’ve got a bunch of databases you need to restore, it’s a giant pain. AmazonRDS has essentially done all the scripting for you.
Amazon RDS makes sure it can always restore your SQL Server to a point in time
When you set up an RDS SQL Server instance, it asks you how long you’d like to keep backups, and if you have a preferred full backup window. And that’s all it asks. Transaction log backups are taken every 5 minutes to support point in time restores, no matter what you want.
So I wondered, what happens if someone creates a database in the wrong recovery model by accident? Does this break the transaction log backups?
Nope! I created a database named DB1_CreatedInSIMPLE using the SIMPLE recovery model. Check out what showed up in the RDS logs right afterward:
How Recently Can I Restore?
You can see log backups in the “Recent Events & Logs” above, or you can just look at the latest restore time for your instance. Since transaction log backups are every five minutes, you can’t always restore to one minute ago.
OK, Let’s Restore This Thing! How Easy Is it?
I wasn’t lying when I said it was easy. You just highlight your instance in the console and select “Restore to Point in Time” under instance actions.
This is going to create a whole new instance using the backups they’ve been taking. You get to pick the time you want to restore from. This will vary depending on how long the instance has existed, and what your backup window is set at. (Mine’s at 7 days, it can go up to 35 days.)
You also get to pick the instance name you’re going to restore to, along with all sorts of properties and security settings:
Setting up the restored instance isn’t immediate. RDS has to set up a new Windows instance and apply a whole lot of your backups, and that takes time.
After you get all your settings done, you click Launch to get your new RDS instance in motion to be created:
Our instance is Cooking
The instance goes through multiple phases. It automatically backs up the instance as part of the process if you’ve got backups enabled, just like any other instance.
Once that completes, I can connect
Here I am, connected to my original and restored instances via SSMS on my desktop!
SQL Server in RDS Isn’t Perfect, But It Does a Lot For You
I am not a founding member of Team Cloud. But I meet a lot of people who struggle making sure that backups are being taken properly and that they can restore. They’re frequently teams of developers who don’t want to learn to deal with backups and restore. They also want things like database mirroring without having to learn it, and RDS handles that for them, too.
This isn’t for everyone. RDS limits the number of databases you can have on an instance. It doesn’t do all maintenance for you — you have to set up your own CHECKDB jobs, for instance, which you can do in the SQL Server Agent. You don’t get tools like Database Mail inside the SQL Server, or the ability to see the SQL Server Error log via SSMS (don’t worry, you can get to that and other logs in the AWS Console). You can’t use every version of SQL Server out there (2008R2 and 2012 only, currently). You can’t do Availability Groups in Amazon RDS, either, just database mirroring. (I’m a big fan of mirroring, so I’m not all that sad about that.)
Putting it All Together
Database as a service is a pretty compelling option these days. It doesn’t take away the role of the DBA, and it doesn’t handle everything for you — but the flip side of that is that you get a lot of flexibility and customization.
And dang, I love the ease of restoring that instance to a point in time.
Brent says: what I love about the cloud is that it takes away the parts of the DBA job that I never really liked in the first place.
Jeremiah says: I’m with Brent – I love getting rid of the parts of the job I never liked. Cloud lets me do that. And RDS lets me ease up management on a certain class of servers even more
Erik says: I, for one, welcome our new Robot DBA overlords.