DP-300 Microsoft Azure Database – Perform backup and restore a database by using database tools – VMs

1. 81. automate backups

Hello. And in this section we’re going to talk about backups. And this is largely concentrating on virtual machines because backups for Azure, SQL Database and Managed instance, they’re automatically done. You don’t need to worry about backups for VMs, you do. Now you can create automated backups if you want. And this is done through the installation of the SQL Server IAS Agent extension to enable automated backups. So this can also be done through the Creative Virtual machine process.

So you need a minimum of Windows Server 2012 and SQL Server 2014 Standard or Enterprise that will allow you access to automated backup version one. If you got something a bit higher, Windows Server 2012 R two or higher and SQL Server 2016 or higher, standard Enterprise or Developer Edition, then you can have get access to automated backup version two. So to do this we go to our virtual machine in the portal and we go down in the settings part to SQL Server configuration and click on Manage SQL Virtual Machine.

Now, you may remember this when we were talking about patching earlier. There are other things like configuring for instance, so we can configure our license to be hybrid or HADR and we can configure the storage so we can extend the drive that we’re using. If we go into backups, we can enable automated backups, so we can specify a retention period for up to 30 days, a storage account, we can have encryption with a password, we can say whether system databases, which are the master model and msdb are backed up or not.

And we can configure a backup schedule. So manual specifies a schedule for full and log backups. Notice no differential backups here, just full and log. So we can have a backup frequency daily or weekly. If it’s weekly, you can do it more than once a week, you can specify which particular days, so maybe you want to do it in work days for instance. And also you can specify a backup start time so you can do it when things are less busy and how many hours you’ve got to actually do the backup. You can also say for the log backup how many minutes should elapse before you have a log backup.

Now, the alternative to this is an automated backup schedule and this will be done based on log growth. So if there’s not much being added in terms of rows to your tables, then it would do the backups less frequency. Now, for this to work, you must use the full recovery model.

After all, full and log backups won’t work in the simple backup model. Now you can back up the default instance or a single named instance. However, if there is no default instance and multiple named instances, then the backup will fail. So you need a default instance, you could have a single named instance and you can back up either one of those or both of them. If you so wish.

But if you got zero default instances, if you’ve got two or more named instances, it will fail. So this is how you can automate your backups in SQL Server virtual machines. And if you’re creating a new virtual machine, you will find this in the SQL Server settings. We have automated patching and automated backups which you can enable or disable. And you can see the same options. So to get into these options to begin with, then go to your virtual machine in the portal, go to SQL Server configuration, manage SQL virtual machine, and then go into backups and enable those automated backups.

2. 90. perform a database backup with options

Now in this video we’re going to have a look at how we can perform a database backup with options. And this is primarily for VMs. We’ll have a look at mis later on in this video but you’ll see why is primarily for VMs. Now to get this we need to go to tasks. So right and click on the database, go to tasks and we have backup as one of the tasks. So let’s work down what we’ve got. First of all we’ve got the database. So in this case this DP 300 VM database.

Then we have got the backup type. So full differential and transaction log. You won’t be able to have that if the recovery model is simple. This is really only for full and bulk logged. But generally you should keep it full unless you’ve got a particular reason. If it’s simple then the only thing you’ve got is a full backup. You can select a copy only backup. So this is independent of the sequence of a backups. And as we’ll see when we have a look at the Mi, this will be enabled by default and you won’t be able to actually change it.

Then we’ve got the backup component. So you can back up the entire database or you can backup specific files and file groups. So this is very useful if you’ve got a huge database and you want to have a backup. You could have a backup just of a particular file group for instance, or a particular file within the file group perhaps. So I’m going to stick with the database and I can back it up to disk. That’s a system file or disk based logical backup device. You may see tape. So that would be a local tape to base logical backup device. So this is deprecated and isn’t really relevant for virtual machines anyway. You may see URL as you do here. So this is for Microsoft Azure Blob storage. Now the content here shows the media contents for the selected disk and tape.

So I’ve got this backup. If I go to content, you’ll see that there’s an error here. So the system cannot find the file specified because that file at the moment doesn’t exist. But you’ll see information here about it. So that’s the backup. But this video is all about the options. So let’s go to the media options. So you can back up to the existing media set, so you can append to the existing backup set, so preserving any prior backups.

Or you can overwrite all existing backup sets replacing any prior backups on this media set. With the current set you can check the media set name and backup expiration. So this requires the backup operation to verify the name and the expiration date. Now if you don’t want to back up to the existing media set, you can back up to a new media set and erase all existing backup sets.

And here you can optionally set the new media set name and the description in reliability, you can verify the backup once it’s finished. You can perform a checksum before writing to the media. So this helps reduce errors when writing to the backup media. However, it will increase the workload and decrease the backup throughput of the backup operation. And we’ve also got continue on error, so continue even if encountering one or more errors. Now, if I was doing a transaction log or backup, then I’ve got some other options here. So I can truncate the transaction log after backing it up. That frees up log space. The database remains online. Alternatively, I can back up the transaction log tail. That’s called a tail log backup. And so that’s the end of the transaction log. Leave the database in the restoring state so it’s not available to users until it’s been completely restored.

So this should only be done when you are restoring your database and you want the very final bit of what’s being written to your database added. Now you’ve also got tape drives here, but again not really relevant for Azure. So I’ll go back to a full backup type and we’ll have a look at the backup options page. So this set shows the name and description of the backup set name the expiration date so it can expire after a certain number of days. So if zero then it never expires. Or you can expire it on a specific date. So that could be useful if you’re having say, monthly backups, weekly backups and you don’t want it to be restorable after say, 714 days.

Now, for the Enterprise edition, you can also select Backup compression and you may also be able to encrypt your backup. But this is only enabled if you append to an existing backup set and you need to back up your certificate or your keys to a different location. You can back up using AES 128, 192 or 256 or triple des. So what I’m going to do is firstly I’m going to script this to a new query window and secondly I’m actually going to run it. So click OK and you can see the backup of the database has been successfully completed. So in the next video, what we’re going to do is start looking at some of this SQL that you can use to back up the database.

Before I do that, let’s just have a look at how we can do this in Mi so here we’ve got our Mi So a right and click on the database, go to Tasks Backup and notice the difference. We have copy only backup checked and that is what we have to do. We have full backup till checked because that’s the only thing we’ve got. You can’t do a copy or a backup of this transaction log say so that reduces what we’ve got for say, media options.

We’re not going to back up to an existing media set because it’s going to be a one off. But we can verify the backup, we can perform the checksum and we can continue on error. And we have the usual backup options. So this is how we can perform a database backup with options so we can right click on this database and go to tasks and backup, but a copy only full backup for Mis.

And why is that? It’s because Mis and Azure SQL database already have configured backups. And if you’ve got a VM with an IAS extension, you can also configure backups in the Azure Portal as we have just seen, but restores if you do that still need to be configured from within SQL Server. So this is how we can perform a database backup with options.