>> In this demo, we're going to take
a look at how you can actually go
through and perform a point-in-time restore after an error occurs.
What we have here is I am in Azure Data Studio,
and I'm using a PowerShell notebooks.
We've seen these before, it basically allows me to
combine text cells and code cells.
Now before I get into it,
I wanted to spend a second to talk about
the process for how we're going to do this.
What happens, and I call this step zero,
is some accident is going to happen maybe you
delete a database altogether or you delete a table,
or you make some other mistake.
After that happens, we're going to use
Azure Log Analytics to determine
the time before the error took place.
Then we can use PowerShell to go through
this whole process of restoring the database,
and checking if the new database is in the right state,
and renaming, and then ultimately deleting the old database.
That's what we're going to do. I'm going to hop over to SSMS,
and I'm connected to my database that
I'm just going to show you this table.
You see we can access this Sales Order detailed table,
and now what I'm going to do is I'm going to drop that table.
If I run this command, again,
you will no longer be able to see it.
The table no longer exists.
What can I do? Now that we've dropped this table,
we need to figure out how to go back in time.
The first thing we need to know is when this table was
dropped so that we can go and restore to a time just before that.
Now, I can do this several ways,
but what I'm going to do is leverage the Azure Portal.
Here I am in my SQL database.
On the left-hand side, I can go down to auditing,
and I can actually select my audit logs.
Now there are lots of logs that are available,
but I can actually leverage some of
the SQL auditing capabilities here
to understand who has dropped a database,
and when it happened.
If I go ahead and run this what you're going to see is
I'm using it on the database.
We saw a drop table for,
and we're looking for any statements
that have the word drop in them.
Then we're going to return the time
along with some other information.
Now you can see this has happened a few times
or I've clumsily deleted this table a few times.
I'm going to pick the most recent one,
and I'm just going to go ahead and copy that time.
Now I can switch back to Azure Data Studio.
If you remember, we're using PowerShell in this case,
and we've determined the time we want to go back to.
Now, the next thing we want to do is actually store that
as a variable here called before error time.
What I'm going to do is go ahead and paste in that value,
and just make sure it matches the required format by PowerShell.
That means adding 12 to this nine to make it military time,
I guess you would say, and I'm going to go ahead and run that.
I actually might want to put it 0.001 milliseconds before just to
ensure that I'm getting the right before this error occurred.
I run that, now I can go ahead and run this command,
and note that we're using
the Azure CLI here that starts with the easy commands.
But we could also use Azure PowerShell
or we could do it straight from the portal using a GUI.
What this command does is it basically says az SQL db restore,
and then the destination name for that database.
This database, it's run on variable,
but basically it's just Adventure Works followed by the word copy.
Then the name of the database we want to restore,
which in our case is just Adventure Works 0602,
and then the time we want to go back to.
This before error time,
which is going to be this time up here,
0.001 seconds before we drop the table.
Now what I can do is I can go back to
SSMS and actually refresh my databases.
What we're going to see is that it's in a restoring state.
Now this can take a few minutes.
This takes just a few minutes because now we're actually restoring
the entire database back on this copy database.
If I refresh again,
what we'll see is that that has completed.
Now as a check, I want to make sure that everything's in
order before I go ahead and replace the other database.
I'm going to do a new query,
and I'm going to copy over this query,
and just see if that table we dropped is indeed back.
We can see that it is.
Now I can switch back to
Azure Data Studio because I'm leveraging PowerShell here.
We've done the point in time restore,
we've also done the next step which is basically to
confirm that we have access to
that table that was previously dropped.
The next step is really to go ahead
and rename the original database.
What I'm doing is taking our original database that has the year,
and changing it to a new name.
I'm basically calling it Adventure Works plus
0406 or whatever that idea have at the end, dash old is.
I can quickly confirm that that's the case by going
back to SSMS and refreshing.
Now I see I have the old one and I also have the copied one.
The next thing I can do is rename
the copy one back to the original name.
Basically, change it from copy back to the original name.
That means if I'm using this database in any applications,
I don't have to change any connection strings.
I'm just down during this brief time,
during the name change.
Again, if I want to double-check before I delete it,
I can see now I have
my original database name back to the state that I want it to be.
Now I can finally delete
that older database because it's no longer needed.
Once that completes, I can confirm with a command
called az SQL Database list dash old table.
It's just going to leverage my default server,
which is the server we've been using,
and list all the databases that are in it.
When we run that, we should be able to see that we just have
our general-purpose database and
a business critical database that I already had before this.
In this demo, you saw how you can use
an Azure PowerShell notebook to very easily
restore to a point in time before an error,
or on a database,
or even if you delete an entire database.
See you in the next video.