DP-300 Microsoft Azure Database – Automate tasks and Perform backup and restore a database by using database tools Part 2

3. Full, differential and transaction log backups

In this video, we’re going to look at how we can perform a database restore to a point in time. And actually this is very easy for Azure SQL database. You don’t have to worry about the backups. Backups are done automatically a full backup, and this is by the way, the same in Azure SQL managed instance. A full backup is done every single week.

So that is a backup of absolutely see everything. A differential backup is done every twelve to 24 hours. So what’s the differential backup? Well, it’s a difference between the current state of the database and the last full backup. So as time progresses until the next full backup, you will notice that the differential backup gets bigger and bigger and then you’ve got a transaction log backup every five to ten minutes. What’s the transaction log back up? It’s everything that’s happened since the last backup.

So whether that last backup is a full backup, a differential backup, or transaction log backup. So we’ve created a database, it’s had a full backup. Next is going to have several transaction log backups. So here’s the first one. So this first one is everything that’s happened since this full backup. So here’s the second one, and this contains everything that’s happened since this transaction log backup. So in other words, it’s not everything since the full backup, but just since this last backup. Then after twelve to 24 hours, so we’ve got plenty of these, we’ll have a differential backup.

So this basically scoops up all of the transaction log backups and a bit more, because this differential backup has everything that’s changed since the last full backup. So if we wanted to restore to this point in time, we don’t need these transaction log backups. They’ll stay because by default you can do a point in time restore backup of existing or deleted databases up to seven days.

By default you can change it to between one and 35 days, apart from Basic, which has a maximum of seven days, and apart from hyperscale. So these transaction log backups are very useful if you want to restore to say, this point right in here. But if you want to restore any later from this differential backup onwards, you don’t need these transaction log backups. So they remain in the system, but I won’t actually need them. So then we do transaction log backups for the next twelve to 24 hours. So this is happening every five to ten minutes.

And then another differential backup. It’s bigger than the first one because it contains everything that’s happened since this full backup. So it contains all of this differential backup and all of these log backups as well. Now, if I want to start to a point of time here or later, I don’t need this differential backup, I don’t need these transaction log backups. So these might as well not exist if I’m restoring to a point of time later than here. If I was restoring to here, then I’ll need them. So then we will have more differential backups, more transaction log backups.

The differential backup is getting bigger and bigger until we get to our next full backup. At this point, I no longer need the previous fall backup, unless I am restoring to a point of time here. Now this new full backup is going to be bigger because it’s going to contain everything. But now I no longer need this full backup. I no longer need this differential backup if I’m restoring to a point of time later than here. And then we have our transaction log backup and our differential backups. And because this differential backup is going from the last full backup, it’s now of a smaller size.

So here are all of the backups that we have done. Now, let’s say I wanted to go to this particular point. I want to restore at this point in time which full backups, differential backups, and transaction log backups do I need? Well, I need this full backup, I need this differential backup, and then I need all of the additional transaction log backups that get me up to that point in time. Now, what if I needed two minutes later? Remember, these are being backed up five to ten minutes. Or do I this transaction log backup records when all the transactions happened?

So I would take this next one and then just have another two minutes. Now all of this goes in the background. You don’t need to worry about all of this. You would need to worry about it if it’s on a virtual machine because you might have to do the backups. But for you, the first backup is scheduled immediately after a new database is created or restored. And unless the data database is large, it usually completes within 30 minutes. So that’s how database backups are done in theory. In the next video, we’ll look at how we can backup in Azure SQL database.

4. 89, 92. perform a database restore to a point in time – test DR

In the previous video we saw how Azure SQL Database and Azure SQL managed instance does full differential and transaction log backups behind the scenes so that you can do a point in time restore. In this video we’re going to see how we can do the restore. So let’s go to the Azure Portal. Now I could go to my database if it is an existing database and click on Restore Store and you will see it’s a point in time restore. We’ll talk about long term backup retention in the next video.

The earliest restore point is on the 7th. So bear in mind I did say that by default there will be seven days worth of backup. So I can’t go before that, but I can choose a specific restore point, any restore point I want. So I could change this to 01:18 A. m. For instance, on another day. Now I would need to put in a database detail. This would be a brand new database. You cannot restore over an existing database. However, you can rename it afterwards. And you will also see that I can’t change the server and therefore where it is located from here now I just click Review and Create and that would have created Afresh. Now if I was going for a deleted database, then I’d go to the SQL Server that is attached to and then on the left hand side we’ve got deleted databases.

So I’ll click on that, I’ll click on one of the databases that I’ve created as part of this course and I will have a point in time restore. I don’t need to worry about all of these details, it will restore to there. But I need to say what restore point do I want? So I want a restore point of 09:00 and I can call this whatever I want. So here is my new database, but I can’t overwrite an existing database. Now you can use PowerShell commandlets to restore databases, but again you can’t restore over an existing one. So having done all this, I’m not going to select an option here I’ll click Review and Create.

So you’ll see, this is DB 300 database two, and just to show that it doesn’t actually exist, I’ll go into SQL Server and I’ll go into SQL databases. It’s no longer there. Click Create and it is being deployed. Now there is another way to restore an SQL database to a different region. In this case, because you notice all of these were restoring to the original server and therefore to the original region. If you are storing to a different region, then what you do is you create a new database. So I’ll create a database, so a single database and I will create it to a different region.

So I will have to create a different server. So create new. So I’m just going to type a different name and then I can create it to a different region. So let’s create it to Australia East and then when I go into additional settings, I can use existing data a backup. This option allows you to restore a database of any server in any Azure region from the most recent Georeplicated backups. So I can say I want the DP 300 and selecting this backup will change something.

So that’s absolutely fine. So that will restore it to a different region. So I have to create the database and then use existing data a backup. So deployment of my deleted database is still in progress. So I’ll just pause the video now. So now it’s been created, I can go to this resource and I can also go to the server and have a look at the databases. And you can see that there is a new database. So in Azure SQL database and Azure SQL managed instance, database backups are done automatically for backups are done every week, differential backups every twelve to 24 hours, and transaction log backups every five to ten minutes.

And then you can use those to restore to a point in time point in Time Restore pitr. So the default is seven days. If we want to change the number of days, then we go in the server down the left hand side. Data Management Backups here we can see what has been backed up. So you can see my previous database, my first database, the main one has got an earliest point in time Restore restore point and I can click on this restore. This is another way to restore a database.

Additionally, I can go to the retention policies and you can also show delete the databases as well. You can also go to the retention policies and click on a particular database, click on Configure policies and say how many days would I like point in Time restore backups to be kept? Notice I can only go up to seven days because this is a basic pricing tier. If I was using another pricing tier, then I can go all the way up to 35 five days if I wish to.

And additionally, I can change how often I’m taking a differential backup. Do I want it every 12 hours or 24 hours? One important thing to remember is you cannot restore a database of an existing database. You would have to name it something else and then you can rename it afterwards. So this is how you can perform a database restore to a point in time.

5. 93. configure long-term backup retention

Now, we’ve also seen in the previous video the term longer term backup retention and we’re seeing it here in the fact. So this is both for Azure SQL database and for managed instance. It should be noted that it is in public preview in managed instance in Azure public regions only at the time of this recording. So we saw in the preview previous video that we can have backups for one to 35 days, but backups can also be configured for long term retention or Ltr. Ltr backups are done by Azure. You can’t control the timing and you can’t manually create an Ltr backup. It may take up to seven days before the first Ltr backup will be shown in the list of available backups.

So ensure if you have a secondary database that you also have an Ltr policy. Because what happens if you’ve got a primary database that fails over to the secondary database? You will no longer have long term retention policies unless it is also configured on the secondary databases. Now, backups are stored in Azure Blob storage and it’s a different storage container each week. Now, to configure long term backup retention, well, we’re already here.

We go into the server, we go into backups retention policies, we select the database and then we configure the long term retention backup. We can say we want to keep weekly Ltr backups and we can say we want to keep them for one week, one month, one year, one day, or however many want. We want to configure monthly LTRs and we want to configure weekly LTRs. And if I’m configuring a weekly Ltr, then you’ll ask me, okay, what week number do you want? Maybe you’ve got a very important week which needs backing up for regulatory reasons, like for instance, maybe the start of the tax year, start of the financial year, if they’re different in your country. So if I click on this, it will do a weekly Ltr backup and keep it for one week. It will do it every month and keep it for one week. But let’s change that to a month and it will do a yearly one and keep it for let’s say up to ten years. So I will click apply. I’m sure I want to have those for this particular selected database.

Now to view the available databases, then we will go to available backups. And if we had an Ltr backup, then we would have the ability to say manage here. We would then be able to click on an Ltr backup and select Restore, which creates a new database or Delete. Now, let’s say I delete the original database. What happens to the LTRs? Well, no more backups are made. How could they? We haven’t got a database and there are not any changes being made.

But in addition, all of the LTRs that we’ve got are retained so we can always restore the database. Now, backups will expire, so they will expire here. Keep week one for ten years. This one, the monthly one, keep for a month. So when backups expire, they are deleted. So bit by bit, you will lose your LTRs, but not immediately.

And this is how you can configure long term backup retention. You go to the server backup, you go to Configure Policies after selecting your database. And you can select weekly, monthly and yearly Ltr backups and sets. Which particular week of the year do you want to keep? And one final thing, if you want to remove LTL settings, then we can do that with this button up here. So all weekly, monthly and yearly policies, but not the backups themselves, will be removed from the selected database. And there you go. They’re removed.