When the unthinkable happens to a SQL database, restoring quickly becomes critical. Learn how to restore both a Point-In-Time backup and a long term backup from the Azure Recovery vault.
- [Instructor] As much as we like to think that nothing ever goes wrong with out databases, in reality, stuff does happen. Someone deletes something, or an application updates a table incorrectly, or maybe the data is corrupt. It doesn't really matter how it happened. We need to be able to get back to the correct state as quickly as possible. Luckily, SQL databases has built in, point-in-time restore or, if you've previously configured it, long-term backups. There are a few things you need to know about the point-in-time backups.
Full, differential, and transaction logs are backed up. Full is a weekly backup. A differential is every few hours. And transaction logs are backed up every five to 10 minutes. And your backups can consume up to 200% of your database size at no additional cost. The length of time that a point-in-time restore is available is dependent on the tier than you select. To recap, basic tier, a point-in-time restore is available for up to seven days. And in the standard and premium tiers, up to 35 days.
We do have a couple restore options available to us. We can restore a backup database. We can restore a deleted database, but only if it's within that retention period. You would not be able to restore a deleted database at the 36th day. You can restore to another geographic region. You can restore from a backup in the Azure Recovery Services Vault. We use this for long-term storage. Let's show you how to restore your point-in-time backups in Azure. I've already logged into Azure, and I'm looking at my resource groups, and I'm going to focus in on the ImplementAzureSQLDatabases resource group.
You're going to notice here that I have several databases and a couple different servers. And I also have a sqlbackups recovery services vault. This was previously created, and in our last video, we configured our database to backup to this vault. For my information on the recovery services vault, see Chapter 5. In the last video, we created the sqldemodatabasesb server and the sqldemodatabases. And then we also replicated those. As you may recall, we also configured the database to back up to longterm storage in that Azure Services Recovery Vault.
But for this demo, I'm going to use a server and database that I've already pre-configured. This allows us to bring up some historical data. I want to go ahead and take a look at the SQLDemo database. I created this several days ago, and from here, I'm going to go ahead and restore. I'm going to click Restore. Our first field, Database name, we can go ahead and take the name that is provided to us, or we can go ahead and create and new one. This is a point-in-time restore, so I'm going to make that very clear, so when this does restore, we know exactly what we're looking for.
I have point-in-time selected. We can see what our oldest restore point is. I'm going to go ahead and just go with the defaults here. But I could go ahead and change that if I wished. Our target server cannot be changed, and we do not have elastic database pools set up, and I'm not going to change the basic pricing tier. I'm going to go ahead and click OK. And this will take a few minutes. While that's restoring, we're going to go ahead and actually restore from our long term backup.
As a reminder, the SQLDemo database is one I created a couple days ago. And because it was created a couple days ago, I'm able to pull some data from longterm storage. Keep in mind, it could take up to seven days before you see longterm backups being available to you. I'm going to change the name, again, so it's very clear for us in the portal that this is our database. And we're going to call it LTR for Long Term Retention. Next, I'm going to select a backup. I only have one available to me at this point in time.
I cannot change our target server. And, as before, I don't have an elastic database pool, and I'm not going to change the pricing tier. And click OK. This will take several minutes for both databases to be restored. Luckily, they're empty databases; it won't take too long. After a few minutes, your restored databases will appear within the Azure Resources manager group. You will see here that I have my SQLDemo, my LTR, my longterm restore, and the SQLDemo_PIT or point-in-time restore.
And these databases are fully functional databases that we can now use as required. To recap, remember that your databases within Azure are backed up, but only to a maximum of 35 days. And this is done automatically for you. If you need to exceed the 35-day-limit, then you will need to configure longterm retention using an Azure Services recovery volt and then manually configuring your databases for longterm retention. By leveraging both of these backup solutions, you're safeguarding yourself against the unthinkable.
- Implementing storage blobs and Azure files
- Managing access
- Configuring diagnostics, monitoring, and analytics
- Enabling and viewing logs
- Implementing Azure SQL databases
- Implementing recovery services
- Creating an Azure Backup vault
- Configuring the Azure Backup agent
- Backing up and restoring files
- Backing up an Azure virtual machine