- What's up, everyone? Welcome to Josh Burns Tech. In this video, I'm gonna give you a step-by-step process that you can take to perform native SQL Server
database backups and restores in AWS RDS for SQL
Server, using .bak files, coming right up. (techno music) So, I've extensively been working with AWS RDS for SQL Server, and I really enjoy the control that you have compared to using
something like Azure SQL, where you don't really have much control as far as what's going on in SQL Server. So, what are some use cases in which you would wanna be able to take native SQL Server backups
in Amazon RDS SQL Server? Let me gives you some
reasons that my clients have been asking for this. Number one, to be able to migrate from an on-premise environment
to RDS for SQL Server and back as well, so data
migration process to and from. The second use case, to
be able to move databases between Amazon RDS SQL Server instances. And the third main use case
is being able to restore a single database, rather than
the entire database instance. Now, there's a specific
process that you have to take to correctly configure all of this, which is why I've decided to do this video and give you my personal steps that I've already went through. So, I do ask one thing of you. That's if you enjoy this
video, you get value from it, it helps you in your job,
please give this video a like and consider subscribing
to the channel as well, so that you'll be updated
when future SQL Server and tech-related content
is uploaded to my channel. And with that being said, let's go ahead and jump into the five steps to enable native SQL Server database
backups and restores. I'm gonna give you guys
a full demo of that. Let's get going. Step number one, we're going to create an AWS RDS option group. So, before we get started
with any of these steps, the first thing that you obviously need is an RDS for SQL Server instance. So, if you don't have
one created yet in AWS, go ahead and do that now. So, in the AWS Management Console, the first thing that we need to do is open our RDS instance. Now, if you've already
searched for it before, it's gonna show up here in
recently visited services. If not, you can click in the search bar, type in RDS, and then click on RDS. And now I'm just gonna
click on DB instances, and then click on my RDS instance. So, you can see here that I'm using the SQL Server Express Edition. This is free to test with
in AWS RDS for SQL Server, so if you wanna go through these steps just to do testing, go
ahead and spin that out. Now, to create our option group, what we wanna do is
click on option groups. Now, this is a default
option group that was created when I created my RDS instance. We're not gonna use it, we're gonna create a new option group, so we
need to click on create group. Option group name, you can
use whatever you want here. I'm just gonna use SQLServerRDSGroup. For the description, you
can use whatever you want. I'm just gonna copy and paste the name. And then, for the engine, you need to use whatever version of SQL Server that your RDS instance is. So, if we look at engine here, we can select sqlserver-ex, which is the express
version that I created. And then we also need to select
the major engine version, which I use the newest one, which is 14 for SQL Server 2017. So, if you're thinking, I
don't know what engine I used, I don't know what version I used, I'll show you how to find that real quick. We can go to databases, right click it, I'm just gonna open up a new tab, or window, apparently. So, right here we can already see that I'm using the Express Edition, but if we wanna see the version as well, I'm just gonna click on my RDS instance. And now, on configuration,
under engine version, we can see that I'm using 14.0. Now I'm gonna go back to my other tab. Everything looks great on this screen. I'm gonna zoom out so you can see the button I'm about to click, and now I'm gonna click create, and now our option group's created. SQLServerRDSGroup, it's
got the right engine, SQL Server Express Edition,
and the version as well, so we're ready to go to the next step. Step number two, add
sqlserver_backup_restore option to the option group. So, now that we have our
option group created, we need to add the
backup and restore option to the option group. So, to do this, I'm going to
select our new option group, and I'm going to click on add option. The option for SQL
Server backup and restore should already be here and be selected, but if for some reason it wasn't, what you could do is
just click this dropdown and then select it, and then
scroll down to IAM role. So, if you're not familiar with IAM, or Identity Access Management, what that's gonna do
is it's gonna allow us to take our RDS instance
and Amazon S3 Bucket, which we're about to create, take these two different components and allow them to
communicate with each other so we can do the backups
and restores using S3. So, under IAM role, I'm
going to click the dropdown and I'm going to select create a new role. Again, you can essentially
name this whatever you want. I'm gonna call it SQLServerRDSIAM. To do native backups
and restores in ADW RDS, you can't use your local PC or
server's hard drive storage. So, the avenue that you have to take to be able to restore your backups is using an S3 bucket. So, if you're not familiar
with AWS S3 buckets, it's basically Amazon's
equivalent to Azure Blob Storage. So, it's an object storage service which allows you to store
your files, known as objects. So, under S3 bucket, if
you click the dropdown for choose an option, if you have any preexisting S3 buckets, they
would be showing up here. There should also be an option, which is like right here somewhere, to create a new bucket. I'm not sure why it's
not showing up for me. It's probably because I'm
using SQL Server Express. When using SQL Server Standard
Edition for my clients, there's usually an option
right here somewhere that you can click and
create a new S3 bucket. Since that option is not
showing up for some reason, what we're gonna do is
just navigate to S3, and then create a bucket from there. So again, if you've been
to the S3 service recently, it's gonna show up in your
recently visited services. If not, you can click in the search box and type in S3, and then
click on it from there. So, first we need to type in a name for our AWS bucket. Now, this name has to be unique in the region that we're gonna be creating the bucket in. So, if you type a name in and it says for some reason that it's not available, that means it's already taken, so just type in something different. So, I'm gonna try joshburnstech-s3. Looks like it's available,
so we can move down. Now, your region is extremely important, so make sure you pay attention to this. Your AWS RDS instance and your S3 bucket have to be in the same region, and by the same region,
it means specifically the exact same one. So, there's more than one US East region. The specific one that
our RDS instance is in is the US Virginia one, so
it's already selected here, US East Virginia. This is the exact same
one as the RDS instance. You just need to make sure those are gonna match up correctly. If your RDS instance and your S3 bucket are not in the same region, we get later on in the video, we're gonna do a backup or restore, you're gonna get an error message, it's not gonna work. So, US East, North Virginia is the correct bucket that I need. Now, there's a next
button behind my webcam. I'll zoom out so you can see it, but I'm gonna zoom back in because this is so small I can't even read it. So, go ahead and click next, and then on the configure options tab, I'm just gonna leave
everything as default here. We don't need to select
anything different, so I'm gonna click on
that next button again, which is behind my webcam. And then on set
permissions, I'm gonna leave everything as default as well. You can go through and look
at blocking public access and what that specifically means, but I'm just gonna leave it as default and you probably should as well if you're just going through these steps. So, I'm gonna leave it as
default and click next. Now you can review all the
options that we just set. If I zoom out, there's an
option to create bucket. This is what I'm gonna be clicking on. So, I'm gonna zoom back in and then I'm gonna click on create bucket. And just like that, we have
our AWS S3 bucket created. We're ready to jump back
over now to the option and finish setting it up. Since I was not able to click the button to create the S3 bucket
directly from the option, I'm gonna have to refresh this page, which is gonna wipe out this name, so I'm just gonna go
ahead and copy it out, then I'm gonna click refresh. If the right option isn't selected, go ahead and change that. I'm gonna just change it to
SQLSERVER_BACKUP_RESTORE. Click IAM role, create new
role, paste our name back in. Our S3 bucket is already selected. Enable encryption, I'm
gonna leave that unchecked. If you decide that you
want to use encryption, you're gonna have to have
the encryption master key any time you do a backup or restore, so just keep that in mind. And then for scheduling, we're gonna change this to immediately. So, as soon as I click create option, it's gonna create it. If you leave it on the scheduled
maintenance window option, you're gonna have to
wait until whenever your predefined maintenance window is. And now we're ready to click add option. Now, eventually we're gonna
need this S3 bucket name, so I recommend clicking on it, and then copying the name. And then once you have the name copied, go ahead and open up Notepad, and then paste your S3
bucket name into Notepad. So now, with our S3 bucket created and our option added to our option group, we need to take a backup of our on-premise SQL Server database and
move it to our S3 bucket. On my local PC, I have a backups folder where I placed a backup of JoshBurnsTech, which was on a local SQL
Server instance that I have. And now we're gonna
take this JoshBurnsTech full database backup, .bak file extension, we're gonna move it to our S3 bucket. So, back in our S3 bucket,
I'm gonna click upload, add files, navigate to
my backup's location, select my database backup, click open, select next behind my webcam. I'm gonna leave all the defaults
selected for permissions, leave all the defaults
selected for properties, and now we're ready to click upload. Again, this is behind my webcam. Now, that upload was
extremely fast for me. It had a lot to do with the size of my database being very small. But be sure to keep in mind that if you have a larger database, this will be the slowest
part of your entire process. For instance, let me give
you a personal example, I was working with a client and they had a 120 gig database, non-compressed. Taking the full backup,
even being compressed, the backup is still around 20 to 30 gig. Now, just to take that 20
to 30 gig database backup, the .bak file extension, and
upload it to an S3 bucket, that process took about four hours. Now, there could've been other
variables playing into that, such as network speed. I would've had to test it, uploading it from a different environment
and different network to validate that, but it
still took four hours. So, if you have a large database and your backup file's large, even being 20 to 30 gig, just be aware that it could take a while to upload. So, again, our .bak full database backup now resides in our S3 bucket. So now we're ready to move onto step three and modify the AWS RDS
SQL Server instance, adding our option group. So, we're gonna go back to databases, click on the RDS instance,
select the modify button, and now we're gonna scroll down until we get to database options. And now, under database options, the default option group
is already selected. We need to select this dropdown and change it to the one we just created. Now we can scroll all the
way down to the bottom, and then click continue,
which is right here behind my webcam. So, you can see in the summary that we're changing the
default option group to the one that we just created. And then, under scheduling, again, we need to change this
to apply immediately so we don't have to wait
for the maintenance window. And as the warning message indicates, this will cause some down time when the instance is being modified, so plan that accordingly. And then now I'm gonna
click on Modify DB Instance. So, you can see under info,
right now it says available. We need to refresh this page. And now under info we
can see it's modifying. This usually doesn't take long. It should just take a few minutes. The RDS instance is available again and now we've successfully
added our option group. Step number four, restore
SQL Server database on RDS instance from S3 bucket. Now that we've successfully
set up everything in AWS that we need to do native SQL
Server backups and restores, we've also set up the
line of communication between our S3 bucket and our RDS instance using the option group, we are now ready to
restore our database backup on our RDS instance. Now, earlier we copied
out our S3 bucket name and pasted it into Notepad. Now what we're gonna do is
we're gonna go into the bucket and we're gonna do our exact same thing with our actual database backup file, so go ahead and copy the name, open up our Notepad file,
and then paste the name. Open SQL Server Management
Studio on your computer, and then connect to your RDS instance. Open a new query window. So, the way that we're
gonna be able to execute backups and restores in AWS RDS is by using some of the RDS
stored procedures that exist in the MSDB database on any RDS instance. So, what I'm gonna do
is paste in some code that I've already modified,
and we're gonna go through it. You can find the same script, I'm gonna put it on a blog
post on joshburnstech.com. It's gonna be linked in the
description of this video. Make sure you check the description out, click on the link, it's
gonna have the code in it that you can go copy
out and then modify it to fit your environment. So, this stored procedure
resides in the MSDB Database. It's called rds_restore_database. It has a parameter on
it for database name, this is whatever you want
the database to be named, and then another parameter
for s3_arn_to_restore_from. This is gonna be your S3 bucket and our backup name that
we copied out to Notepad. That's where this is gonna come into play. So, first up we have database_name. Again, this is whatever you want your database name to be. We took a backup of the
JoshBurnsTech database, so I'm gonna name this JoshBurnsTech. S3_arn_to_restore_from, arn:aws:s3, all this is gonna remain the same, no matter where you're running this from. Do not change any of this part. The first part, obviously,
we need to change is bucket_name. This is gonna be our S3 bucket name, so we're gonna open up Notepad, we're gonna copy our bucket name. So, we're gonna open our Notepad file, we're gonna delete off bucket_name, and now we're gonna
paste in our bucket name. And then lastly, we need
to change bakup_file. So, this is bakup_file,
it's just a placeholder. We need to delete this, and
then we need to paste in our backup file name that
we copied into Notepad. So, again, I'm gonna
open the Notepad file. Now I'm gonna copy the backup file name, I'm going to delete off bakup_file, and paste in the backup file that we took. Now, just to do a quick run through to make sure everything
looks the way it should, we're executing the correct
MSDB restored procedure, we're using the database
name that we wanna use, and we're using the correct bucket name, and our backup file as well. Make sure that you get
the extension, .bak. If you don't, it won't restore. Now, before I hit execute, that's gonna start the restore process, the way that you're able to monitor the progression throughout
the restore process is using another stored procedure that exists in MSDB Database. So, I'm gonna go ahead
and paste this in as well. So, for this stored procedure, I'm also gonna put it
in the blog post as well in a code block that you can copy out, so make sure you check the
description of this video. So, first we're gonna highlight
and execute our restore. And again, in SQL Server,
if you highlight any code, it's only gonna execute the
code that you highlight. So, I'm gonna go ahead and click execute. Now, as soon as you hit execute, it will return one record. It's gonna show that
you started the restore, when it was created, the database name, bucket name, everything like that. Now what we're gonna do
is we're gonna execute the MSDB rds_task_status,
and it's gonna give us the status of this restore. Again, I'm gonna highlight
the code, click execute. We can see the task type,
we're doing a restore, the database name we chose, JoshBurnsTech, the percent that the restore's complete, we're currently at zero
since we just started it, the duration of the restore, last updated, and when
the restore was created, and the S3 object that
we're using as well. Now, while this is running, you can just continue to hit
execute on the task status. This database is really small, so it shouldn't take too long. I'm just gonna keep
continuing to refresh this until it's finished. So, here's a great lesson
in troubleshooting for you. I took our database backup on
a SQL Server 2019 instance, the preview version of it,
since it's not even out yet. I took that backup on my local computer using SQL Server 2019. If we scroll over, we can see, and what I did is I just
copied out task info and pasted it into the query window. So, we scroll over, we can see
a list of database versions. In RDS, it only has production SQL Server instances right now, which goes up to 2017, so 2019
isn't available in RDS yet. It's not available at all for public, it's just in preview, so
that's why this backup failed. So, we scroll over, "This
version is incompatible "with this server, which
is running version 14.0, "SQL Server 2017. "Either restore the database on a server "that supports the backup, "or use the backup that is
compatible with this server." It says it's terminated abnormally. So, if you do a restore,
you do a backup in AWS RDS, run that task status through a procedure that I pasted in. When you do that, if something fails, you're gonna see it in
the task info column. It's also gonna list in the lifecycle that it failed as well, with an error. It's also gonna show
in the lifecycle column that there was an error as well, so this is how you
would troubleshoot that. So, what I'm gonna do now is just log into a SQL Server 2017 instance, create a JoshBurnsTech database, take a backup of it, move it to S3, and we'll jump back in and do this again. All right, so we're back. I created a JoshBurnsTech database on a SQL Server 2017
instance, put one table in it, took a backup of it, moved it to S3, we're ready to restore again. So, I used the exact
same backup file name, so we can just run the
stored procedure again without changing any parameters. So, we can see it's created, we can start running the task status. There's our new entry, it just started. And as you can see, it
just completed 100%, so now we can refresh our RDS instance and our new database should be there. So, I'm gonna go over to databases, do a refresh, and there's our new database that we just successfully
restored from our S3 bucket. Now I can expand my database, and there should be one table. So, there's my tech
videos table I created. We can look at the data in it. It should just be one record. And there's the one
record, AWS RDS SQL Server. So, let's move onto step five and backup our SQL Server database on our RDS instance to our S3 bucket. So, I'm gonna select the
JoshBurnsTech database, I'm gonna go to new query. So, in AWS RDS for SQL Server, to enable backup compression, you have to run a script to do it. You can't go through the GUI like you normally would and
just right click the instance and then go ahead and do that
for your database settings. You have to run a stored procedure. So, I will also throw this
on the blog post as well that's gonna be linked, and
there'll be a code block for it. So, this stored procedure executes out of the rdsadmin database. You can see it over here in databases. This database gets created on any RDS instance for SQL
Server when you create it. So, it's executing out of rdsadmin, it's calling rds_set_configuration, S3 backup compression true. So, after executing this,
when we take a backup in SQL Server RDS, that backup file is gonna be compressed in AWS S3. So, it executed successfully, now our backups are gonna be compressed. Now I'm gonna go ahead and
paste in the stored procedure that allows us to do the backup. This is also gonna be in a
code block on the blog post. So now we're just gonna go
through and modify this. So, this stored procedure, again, executing out of MSDB, it's
called rds_backup_database. So, instead of rds_restore_database, it's rds_backup_database. Source database name,
we need to change this to our database name. Again, I'm gonna use JoshBurnsTech. S3_arn_to_backup_to, this
is where we wanna backup to in our S3 bucket. I'm just gonna to back
to our other query window and I'm gonna copy this out. So, I'm gonna go ahead
and copy the full path, but we'll go in and
change the backup name. So, starting with bucket name, we gotta highlight over,
and then do a paste. So, again, we're calling
MSDB rds_backup_database, we're using JoshBurnsTech
as a source database name, which is on our RDS instance, and then we're gonna be backing up to our S3 bucket, joshburnstech-s3, and we're gonna change
the backup file name, I'm gonna delete this off, and we're just gonna give
it today's date on the end. Today's actually the eighth. And then lastly, if this backup file existed for some reason,
we're gonna overwrite it. So, I'm gonna go ahead and highlight this stored procedure code, hit execute. There's our initial
record basically saying that we just started the backup. So now I'm gonna highlight the rds_task_status stored
procedure and execute it. So, we can see that the
backup is in progress, nothing's failed yet. It's zero percent complete, so we're just gonna keep refreshing until it's done. So, it just failed, so we're gonna do a little bit more troubleshooting. So, again, I copied out
the task_info column, and now, scrolling over, we can see that since I'm using SQL Server Express, I cannot compress backups,
so that's something that's been known forever, I just neglected to remember it when I was actually setting
up compression on here. So, we're not gonna be
able to use compression, so I'm just gonna scroll
back up to the top and I'm going to change
this value to false. And now I'm just gonna execute this again. So now we've turned compression off. I'm gonna highlight our
stored procedure again to do the backup and run it again. Now I'm gonna select task_status, execute. We can see that the backup
says it's 100% complete now, so it should be showing
up in our S3 bucket, so we're gonna go check that out. So now we're back in the
AWS console and S3 buckets, we're gonna open up our bucket, and there's our new database backup. So, with this backup, what we could do is we could restore this database backup onto a different RDS instance, we could copy it down locally to an on-premise SQL Server environment, restore it onto on-premise SQL Server. There's a lot of different
things you can do. So, this gives you the flexibility to take a backup of your
RDS SQL Server database, move it to wherever you want, it gives you that flexibility. Again, I've recently had clients that wanted to take a .bak file backup of their RDS database,
be able to move it out to an on-premise SQL
Server to do development and things like that, so
that's a use case for this. Obviously, another use
case is redundant backups. You can never have too many backups of a production SQL Server database, and you'll remember that and be thankful. If you have some type of
issue like corruption occur, it's always good to
have redundant backups. So, overall, I've been
really impressed with AWS RDS for SQL Server. I love the granular control that you have, unlike something like Azure SQL. If you're a DBA or you've been working with SQL Server a lot, you're gonna appreciate
what AWS RDS can give you. Now, if you enjoyed this
video, it helped you out, please throw a comment
down below and let me know. Also, if you run into any
issues during this process if you're testing it,
also leave those comments, I will be glad to help you. On this end screen, I recommend
checking out these videos, and be sure to hit that
round subscribe button for weekly tech videos. And hit that notification bell so you don't miss anything. Thank you for watching,
and until next time. (hip-hop music) ♪ All about the pension man ♪ ♪ Hustle like a lot of us ♪ ♪ Man I'm stressing out ♪ ♪ I just can't help
but pull a bottle off ♪ ♪ My girl ♪
✅ How do you Restore SQL Server Databases and take Native SQL Server Database Backups in AWS RDS for SQL Server? This Vdeo covers a step-by-step process that you can implement in your AWS RDS for SQL Server Environment.