>> We're starting a New SQL Insider Series where you
can learn from the experts about various SQL topics.
Join us in the first episode to learn about restoring backups from
long-term retention in Azure SQL database
this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed SQL Insider Series.
This is the first time we're doing
this series or the first episode in this series.
We're really excited about it and we're kicking it off
with Laura Grob as Cloud Solution Architect.
Laura, thanks so much for coming on the show.
Can you tell us a little bit about yourself,
what you do, and this new series?
>> Absolutely, Anna.
My name's Laura Grob as Anna said,
and I'm a Cloud Solution Architect in
the banking sub-vertical within financial services.
I work with a lot of customers
architecting solutions across our Azure data platform,
so everything from SQL DP,
Azure Data Factory,
SQL MI, and
it's been a lot of fun so far. It's been a great journey.
>> Awesome. Great. We're happy to have you on the show,
and we're happy to be kicking off this new series
where we talk about different things and customer scenarios.
In this one, we're going to be
talking about restoring a backup from
long-term retention in Azure SQL database
and I wanted to kick off by
just asking you a little bit about
the customer scenario where you saw this.
>> I have a customer where
they had created their Azure SQL database,
they had created the logical server,
and then they went and they deleted the logical server,
which removes the database and
also it removes all the short-term backups.
They then decided they wanted
that backup back from the database they had deleted,
and so, fortunately,
they had configured their long-term retention.
We were able to go back into
the long-term retention because the backups hadn't expired yet,
and we were able to pull out and
restore a backup from there to get them their data back.
>> Got it, and can you just explain for some of
our viewers who might not be familiar with long-term retention.
Folks might think, if I delete
all the storage where all the backups
are there is no long-term retention. How does that work?
>> Within Azure SQL database,
you can go in and you can configure
what your long term retention is,
and a lot of customers do this for legal and compliance reasons.
So they'll need to keep maybe a monthly backup and they
might need to keep it for 10 years or
an annual backup for X amount of time,
and so you go in separately and you can configure this
based on whatever your business requirements are.
Like I said, it's usually legal and
compliance that comes into play here,
so those will expire obviously once the retention period is met,
and in this customer scenario,
the backup is still available because they caught it soon
enough that the retention had been expired yet.
>> Got you. Cool. I think I'm on board with what's going on.
How did you understand that these backups were still available?
How were you able to discover them?
>> We had some PowerShell commands that let us go and look and
explore and see what's in long-term retention,
and then we were able to select which backup we
actually wanted to restore based on what was still available.
That's part of what I'd like to demo is
just going through and showing
here's how you can view what's in
your long-term retention and
some different ways of breaking it out,
and then how you go about doing the restore.
>> Awesome. That sounds pretty cool.
Let's dive right into it.
>> As you can see here, the first thing you have to do, obviously,
is log into Azure,
enter and set your context for your subscription.
The PowerShell command that we focused on here
is this AzSqlDatabaseLongtermRetentionBackup,
and it's got a list of parameters that you can specify.
So for example, if I do -locationwestus3,
what's going to come back is all of
my backups that are in this region,
that are in long-term retention.
But I can get more granular.
In my case what I set up for this demo,
I had created a logical server called lgdemo5,
and if I run this command,
it will show me all of
the long-term retention backups that are still
available for databases on this specific logical server.
I run this, you can go through and you can see
obviously what the expiration date is,
the backup names, the backup times,
the location, the server that came from your database name.
In this case, deleted DB.
So I know that I have a deleted DB that's available.
I could go in and I could see all of the long-term backups for
just that database by specifying the database name parameter.
I can also reduce
the list even further and just have
it show only the latest for database.
In this scenario, I'm only going to get
one back instead of the four or five that we were seeing before,
and so this shows,
again, the time of that.
It shows when it's set to expire so,
in this case, this backup would expire June 4th.
The backup time though was from April 9th.
Again, it gives you-all the information that you need,
and to actually perform the restore,
I need this resource ID,
which is a pretty long string in this case.
You'll notice that if I look at my Restore command here,
I'm populating some variables.
I'm populating the resource ID by pulling in
that last database backup for my deleted DB.
I also need to specify my target server.
Because lgdemo5 is gone,
I have a logical server that I had already
created that doesn't have any databases on it,
and that's called lgdemo6.
Then I give it a target database name,
so my "restoredeleteddb" is my target name.
I have to tell the resource group.
In my case, my resource group name is the DemoRG,
and then I run the Restore command,
and I specify that I'm restoring from long-term retention backup,
and then I feed it this resource ID,
the resource group name,
the server name, which is my target server name,
and the target database name.
When I run this, it's going to take a couple of minutes
to run because it's going to actually have to go
get that backup from the long-term retention and it's going to
restore it on to my logical server lgdemo6 as a new database.
We'll run that now.
[MUSIC]
>> Now that that's finished,
let's go take a look in the portal.
You can see this is my lgdemo6
and you can now see that I have a "restoredeletedDB" present,
and you can also see that this is a Gen5.
2vCores.
One of the other cool things that I can do while I'm doing
this restore is I can come
in and I can change my service level objective.
For example, I can specify all of the same parameters above
and then I can add in this addition compute generation and vCore.
In this example, I could go ahead and I could
restore this to a 4 vCore machine,
so I could go higher or lower.
Just be aware that if you're going to go lower,
it has to have enough storage capacity based on
the vCore count to accommodate or your restore will fail.
We can go ahead and we can run this command.
>> Now while that kicks off,
Laura, I'd love to ask you,
what are some scenarios where you've
seen or have you seen scenarios where
a customer would actually want to scale up,
scale down, switch additions when they do
this restore from the long-term retention?
>> Yeah, because the reality is
sometimes they need it so they can access
a piece of data they don't need to run
a full workload against it so they might not want to
spin it up as high of a vCore account.
They want to save some cost if they can and so,
in that scenario, you would spin up a smaller machine.
Since mine was already pretty much
the smallest vCore account you could get,
I was going the opposite direction,
which could also come into play if,
for example, you realized you had deleted it but
maybe you had a bunch of folks that
wanted to run some reports against it,
so then you might actually have more workload against it.
It might have been an archive database,
but maybe you had a legal scenario and now
several auditors need to write reports at the same time.
>> Got you. That makes sense.
In this one, in the results,
we see that the previous one,
I guess, it was a 2vCore.
>> It is a 2vCore, right.
>> Okay, so then when this one completes, we'll see?
>> We'll see a 4vCore.
>> Okay, cool. Let's see what it looks like.
[MUSIC]
>> Now if we go into the portal and we take a look at my lgdemo6,
you'll see that we now have this changed
vCore database and that one is a Gen5 with 4vCores.
You can see that we were able to change
the SLO while we were performing a restore.
>> Awesome. That's pretty cool.
It's been really useful for myself to
see all the different options that come into
play when you're thinking about restoring
a long-term retention or an older backup,
and so it's been really useful so thanks.
I think it's been useful for our viewers as well.
As we wrap,
are there any final tips, or tricks,
or words of advice for folks as they
start to use these capabilities?
>> One other thing we didn't show here,
but we actually modified one of the DMVs that used to show
that we could see if you were zero percent complete, 50 percent,
or 100 percent complete on your restore,
it's now more granular and you
can see the exact percentage complete,
so that was a change they made to that DMV.
If you're trying to track where you are in
the process of your restore, it shows that now.
>> Awesome. Cool. Laura,
thanks so much for coming on the show.
We're really excited about this SQL Insider Series.
See what else is coming down the pipeline.
To our viewers, if you like this episode,
go ahead and give it a like,
check the description for links for learning more,
and we hope to see you next time on Data Exposed.
[MUSIC]