You're reading from Professional Azure SQL Managed Database Administration - Third Edition

Product typeBook

Published inMar 2021

Reading LevelBeginner

PublisherPackt

ISBN-139781801076524

Edition3rd Edition

Languages

SQL

Tools

Azure

Concepts

Databases

Authors (2):

Ahmad Osama

Shashikant Shakya

View More author details

5. Restoration

In the previous chapter, we talked about performing database backups for Azure SQL Database and Azure SQL Managed Instance. Similar to a database backup, a restore is another housekeeping activity that a DBA performs, whether to move or copy a database from one server to another or to recover from an outage or an accidental update/delete operation. In this chapter, we'll look at different ways to restore an Azure SQL Database and an SQL Managed Instance database.

Azure SQL Database and SQL Managed Instance have the following restore options:

Point-in-time restore
Restore from long-term backup
Restore a deleted database
Geo-restore a database
Import a BACPAC – only for Azure SQL Database

Figure 5.1 shows the types of backup available with Azure SQL Database, the different restore options, and the ways to restore a database:

Figure 5.1: Types of database restore

A restore can be performed...

Restore types

This section discusses the different types of restore available in Azure SQL Database and SQL Managed Instance.

Point-in-time restore

Point-in-time restore (PITR) isn't new in the world of SQL Server. On-premises SQL servers allow you to restore a database to a particular point in time by specifying the point-in-time option when restoring the database using the restore command.

Azure SQL Database and SQL Managed Instance backups are managed by the automatic backups feature of the PaaS offering. Point-in-time restores can only be performed using the Azure portal, PowerShell, the Azure CLI, or the Azure SDK. PITR uses the automatic Full, Differential, and Log backups.

For all new, restored, and copied databases on Azure SQL Database and SQL Managed Instance, the default retention period is 7 days. The retention period can be adjusted for existing or deleted databases (the latter is only available on SQL Managed Instance):

For active databases on...

Restoring deleted databases

Azure allows you to restore a deleted database to the time it was deleted, or to any time within the retention period. You can select the deleted database you wish to restore from the pool of deleted databases. You are able to restore a deleted database because the automatic backups are saved for a given retention period that depends on the service tier.

Restoring a deleted database on Azure SQL Database using the Azure portal

Let's restore a deleted database using the Azure portal:

Log in to the Azure portal using your Azure credentials. Open All resources from the left-hand navigation pane.
From the All resources pane, open the Azure SQL server that hosts the deleted database you wish to restore.
In the Azure SQL Server pane, from the Settings section, select Deleted databases:
Figure 5.19: Navigating to deleted databases
The Deleted databases pane lists the databases and their deletion times. Select the toystore database...

Geo-restoring databases

A geo-restore allows you to restore a database from a geo-redundant backup to any available Azure SQL servers and SQL Managed Instance, irrespective of the region.

The automatic backups of Azure SQL Database and SQL Managed Instance are geo-redundant by default, and are copied to a paired Azure region as and when they are taken. There is a maximum delay of one hour when copying a database to a paired geographical location. Therefore, in the case of a disaster, there can be up to an hour of data loss:

Figure 5.26: Geo-replication to a different geographical location

Geo-restore can be used to recover a database if an entire region is unavailable because of a disaster:

Using geo-restore in the case of the unavailability of an entire region

Figure 5.27: Use of geo-restore in the case of unavailability of an entire region

The most recent full and differential backups are used to perform a geo-restore.

Geo-restore doesn't support PITR. It is the most basic disaster...

Importing a database (Azure SQL Database only)

You can import a database into an Azure SQL server from a BACPAC or DACPAC file kept in Azure Storage. The import operation will create a new Azure SQL database from the BACPAC file.

The BACPAC file can be imported to any of the available Azure SQL servers in any given region. This can be useful for quickly creating new test environments.

The import can be done through the Azure portal, PowerShell, the Azure CLI, or the Azure SDK. Let's learn how to import a database from a BACPAC file kept in Azure Storage. Open the Azure portal, go to https://portal.azure.com, and log in with your Azure credentials:

From the left-hand navigation pane, open the All resources section. Select the Azure SQL server you wish to import the database to.
In the Azure SQL Server Overview pane, select Import database:
Figure 5.35: Selecting the Import database option
In the Import database pane, under Subscription, select your Azure subscription...

Activity: Performing a PITR for an Azure SQL Database with PowerShell

Consider the following scenario: Mike is a new DBA, so his trainer is aware that there might be some misses at his end. Therefore, his trainer wants to configure PITR on the databases that Mike is working on. In this section, we will perform a PITR using PowerShell by following these steps:

Press Windows key + R to open the Run dialog box. In the Run dialog box, type powershell ise to open a new PowerShell editor window.
In PowerShell, click File from the top menu and then select New to create a new PowerShell script file:
Figure 5.40: Creating a new PowerShell script
In the new PowerShell script file, copy and paste in the code from the following step.

Define the PowerShell script parameters. The parameters are self-explanatory:

param(
            [Parameter(Mandatory=$true)]
          ...

Activity: Performing a geo-restore of an Azure SQL Database with PowerShell

Let's once again consider our example of ToyStore Ltd. Mike is aware that, although on the cloud, his data is still physically stored on servers. Hence, there is a possibility of data loss due to natural disasters. In these instances, he would have to perform a geo-restore operation. This section makes use of PowerShell to perform a geo-restore:

Press Windows key + R to open the Run dialog box. In the Run dialog box, type powershell ise to open a new PowerShell editor window.
In PowerShell, click File from the top menu, and then select New to create a new PowerShell script file:
Figure 5.42: Creating a new PowerShell script
In the new PowerShell script file, copy the code as instructed in the following steps.
Define the PowerShell script parameters. The parameters are self-explanatory:
```
param(
           [Parameter(Mandatory=$true)]
 ...
```

Activity: Performing Point-In-Time restore for SQL Managed Instance with PowerShell

In this two-part activity, we will be performing a point-in-time restore of a database in SQL Managed Instance using PowerShell and then a cross-instance restore of a database (from one SQL Managed Instance to another SQL Managed Instance).

Part 1: Restoring a database to a point in time using PowerShell on one managed instance

To perform a point-in-time restore operation on the same SQL Managed Instance using PowerShell commands, please follow these steps:

Press Windows key + R to open the Run dialog box. In the Run dialog box, type powershell ise to open a new PowerShell editor window.
In PowerShell, click File from the top menu and then select New to create a new PowerShell script file:
Figure 5.44: Creating a new PowerShell script
In the new PowerShell script file, copy in the code from the following step.
Define the PowerShell script parameters. The parameters are self...

Activity: Geo-restoring a database hosted on SQL Managed Instance using the Az PowerShell module

Geo-restoring allows you to recover a database to a different region. This can be used as a database recovery solution, but the recovery time depends on the database size. Geo-backups are only available when SQL Managed Instance is configured with geo-redundant backup storage.

Let's quickly go through how to perform a geo-restore using PowerShell.

This activity is similar to the previous point-in-time restore we did using the Az PowerShell module, so all the steps are not covered here. We are only covering changes in the script.

SQLMI_GeoRestore.ps1 is available in the source code and most of the code is similar to the point-in-time restore activity.

In our geo-restore PowerShell script, we are using the same Restore-AzSqlInstanceDatabase command, but instead of using the -FromPointInTimeBackup parameter, we are using the -FromGeoBackup parameter.

The following is a...

Activity: Restoring a deleted database on SQL Managed Instance using PowerShell

Restoring a deleted database on a managed instance can be done using the Azure portal, as we saw in the demonstrations earlier, and PowerShell. Cross-instance restoration for deleted databases can only be done using PowerShell.

Like active database point-in-time restore, cross-instance restore for deleted databases is only available in the same region and same subscription.

To restore deleted databases using PowerShell, you can use the following PowerShell scripts; these scripts are similar to the point-in-time restore and geo-restore scripts.

SQLMI_DeletedDatabaseOnSameInstancePITR.ps1
SQLMI_DeletedDatabaseOnCrossInstancePITR.ps1

This concludes the chapter. As we have seen, Azure SQL Managed Instance supports restoration for all possible scenarios. Use these PaaS capabilities to easily restore your managed databases to the same instance or across instances in the same region and...

Summary

Restoring an Azure SQL database and Azure SQL Managed Instance is different from restoring an on-premises SQL Server database. In this chapter, you learned about the following restore options:

Point-in-time restore
Restoring a deleted database
Geo-restoring a database
Restoring a database from a long-term backup
Importing a BACPAC file

Each of these options can be leveraged in different scenarios. For example, a PITR will help you recover from a corrupt database or accidental deletion in Azure SQL Database and SQL Managed Instance, whereas importing a BACPAC file in Azure SQL Database helps you set up a development environment with the same schema and data across development, testing, and integration.

In the next chapter, we will look at the security mechanisms available to secure an Azure SQL database and SQL Managed Instance.

The rest of the chapter is locked

You have been reading a chapter from

Professional Azure SQL Managed Database Administration - Third Edition

Published in: Mar 2021Publisher: PacktISBN-13: 9781801076524

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (2)

Ahmad Osama

Ahmad Osama works for Pitney Bowes Pvt. Ltd. as a technical architect and is a former Microsoft Data Platform MVP. In his day job, he works on developing and maintaining high performant, on-premises and cloud SQL Server OLTP environments as well as deployment and automating tasks using PowerShell. When not working, Ahmad blogs at DataPlatformLabs and can be found glued to his Xbox.
Read more about Ahmad Osama

Shashikant Shakya

Shashikant Shakya is a passionate technologist with decades of experience in the sphere of databases. He works for Microsoft as a senior support engineer. In his day job, he works on Azure SQL Database, Azure Database for MySQL, and PostgreSQL. Apart from his work, he is a regular speaker at the SQLBangalore community group.
Read more about Shashikant Shakya

Other recommended products

Related to this chapter

Professional Azure SQL Database Administration

As the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. It’s important to know how to administer SQL Database to fully benefit from all of the features and functionality that it provides. This book addresses important aspects of an Azure SQL Database instance such as migration, backup restorations, pricing policies, security, scalability, monitoring, performance optimization, high availability, and disaster recovery. It is a complete guide for database administrators, and ideal for those who are planning to migrate from on premise SQL Server database to an Azure SQL Server database.

BookJul 2018398 pages

Azure Data Engineering Cookbook

This book will help you design and implement modern ETL workflows along with data management, monitoring, and security aspects to meet the current organization's needs. You will use various services such as Azure Data Factory, Azure Databricks, Azure Stream Analytics, and Azure Data Explorer to design efficient data processing solutions.

BookApr 2021454 pages

Learning Microsoft Azure Storage

Microsoft Azure Storage is the bedrock of Microsoft's core storage solution offering in Azure. No matter what solution you are building for the cloud, you'll find a compelling use for Azure Storage. This book will help you get up-to-speed quickly on Microsoft Azure Storage by teaching you how to use the different storage services. You will be able to leverage secure design patterns based on real-world scenarios and develop a strong storage foundation for Azure virtual machines.

BookNov 2017276 pages

Professional SQL Server High Availability and Disaster Recovery

The Professional SQL Server High Availability and Disaster Recovery book explains the high availability and the disaster recovery technologies, their technical implementation, and different topologies that you can use when creating a highly available infrastructure with hybrid topologies.

BookJan 2019564 pages

Learn Microsoft Azure

Microsoft Azure is a cloud computing platform that helps you build, deploy, and manage applications to overcome your business challenges. This book covers the commonly used Azure services and also explains how you effectively integrate and utilize them.

BookDec 2018354 pages

SQL Server on Azure Virtual Machines

This book will teach you how to harness the capabilities of SQL Server and the cloud database by taking you through the steps to deploy the server in Azure virtual machine effectively.

BookJun 2020200 pages

Limitless Analytics with Azure Synapse

This book helps you understand the basic concepts and techniques of using Azure Synapse step-by-step. You'll gradually gain the skills you need to work with data and develop analytics solutions using the Azure analytics platform even with no prior knowledge of Azure.

BookJun 2021392 pages

SQL Server 2019 Administrator's Guide

This book will give you all the information you need to become an expert database administrator and master the administrative aspects of SQL Server 2019. From setting up and configuring your SQL Server instance to fine-tuning your database, this extensive guide will teach you the nitty-gritty of SQL Server 2019 administration.

BookSep 2020522 pages

Learn T-SQL Querying

T-SQL is an extension of the SQL language which allows you to tackle advanced querying and query-tuning challenges in SQL Server and Azure SQL Database. This book will be a perfect reference for you to write more efficient T-SQL code to perform simple-to-advanced tasks for data management and data analysis.

BookMay 2019484 pages

Introducing Microsoft SQL Server 2019

Introducing Microsoft SQL Server 2019 takes you through what’s new in SQL Server 2019 and why it matters. After reading this book, you’ll be well placed to explore exactly how you can make MIcrosoft SQL Server 2019 work best for you.

BookApr 2020488 pages

SQL Server 2017 Machine Learning Services with R

With integrated R Services within SQL Server 2017, developers and data scientists can now benefit from the integrated, effective, efficient and more streamlined analytics environment. In this book, you will understand how to leverage the capabilities of R Services in SQL Server 2017. This short yet effective guide will help you get familiar with SQL Server 2017 R Services, and will show how to implement efficient data science models using it.

BookFeb 2018338 pages

Azure Strategy and Implementation Guide

This book will teach you to migrate your organization’s business operations from local data centers to the Azure cloud platform and manage them to enhance overall efficiency.

BookMay 2021228 pages

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages