In last week’s post, I talked about the steps required to backup a Azure SQL Database. That post leaves us asking what’s the use of having a backup of a database, if you aren’t going to restore it somewhere. And more importantly, if you can’t restore a database, or don’t know how, then the backup has no value. Thus, restoring is the other half of the coin that is as critical as the first half.
In today’s post, I’m going to take the backup that was previously created and restore it to a local SQL Server instance. Personally, I first needed to do this when I wanted to do some development work on one of my Azure SQL Databases while I was offline. I could have used SQL Server Data Tools but wanted to have a fully functional database that I could redeploy to Azure when I reconnected. Hence the local restore.
The Keys to Restoring
The first step to restoring a backup of an Azure SQL Database to a local server is getting access to the backup of the database. This might seem to be an obvious step, but it lends itself to the question of how a backup created within the Azure will be made available to a local instance. There are a number ways to accomplish this need. As I mentioned in the previous post, one of the options is to download the file from the Windows Azure portal. There is another way that removes the need to perform the download manually.
Instead, the download of the bacpac backup file can be performed as a part of the restore. To accomplish the restore in this manner, the Access Keys for the storage account are required. The Access Keys are accessible from the Storage Account list by clicking the Manage Access Keys button. The button can be found in the bottom menu bar, indicated by an orange arrow in Figure 1.
Figure 1. Storage Account List
The Manage Access Keys window provides the current access keys for each storage account, shown in Figure 2. The access keys allow secured connections to storage accounts; which can be used by SQL Server Management Studio to retrieve bacpac files. There are two access keys for each storage account. Either key will work for the restore, select one of the keys and copy the value.
Figure 2. Manage Access Keys Screen
Executing the Restore
With the keys in hand, the next step in restoring a Azure SQL Database locally is to right-click on the Database container in SQL Server Management Studio. From this context menu, shown in Figure 3, select the option to Import Data-tier Application. Contrary to backups from on-premise SQL Server instances, the backup format for Azure SQL Databases is a data-tier format, or bacpac. This format contains all of the schema and data for a database.
Figure 3. Right-click Menu for Databases in SQL Server Management Studio
After selecting Import Data-tier Application button, the Import Data-tier Application wizard initiates. The first screen in the wizard is the obligatory Introduction screen, shown in Figure 4. This screen provides a description of the restore, or import process. From the perspective of restoring a Azure SQL Database, those two terms are synonymous. The biggest, and only, decision on the screen is whether the Introduction screen should show up on any future restores.
Figure 4. Import Data-tier Application Introduction Screen
The next screen in the wizard is Import Settings. Since this restore is assuming that the bacpac file has not been downloaded, select the Connect button, located in the middle-right of the screen. The Connect to Windows Azure Storage window then appears, shown in figure 5. Type in the name of the Storage Account and paste in the Access Key, retrieved previously. Either the primary or secondary key will suffice.
Figure 5. Connect to Windows Azure Storage window
After the connection is secured, the drop-down box for the available container will populate. Select the container and then the file from the list provided. If done correctly, the backup file will be listed. Lastly, double-check the location for the temporary file. A temporary file is created for the backup while it is being restored. For all intents, this is the same as a manual download of the bacpac file. The temporary file location will need to be large enough to store the backup file. When finished, select the Next button.
Figure 6. Import Data-tier Application Import Settings Screen
The next wizard screen is the Database Settings screen. There are few options for the database. The first is the database name; which will default to the name of the bacpac file. The next two are the names of the data and transaction log files for the database. By default there will only be the two files. If more files, or filegroups, are desired, or needed, the database will need to be updated after the restore. Click next to advance to the next screen.
Figure 7. Import Data-tier Application Database Settings Screen
Over the last two screens in the Import Data-tier Application wizard, there were few options to set for the restore operation but they are all that is needed. The next screen summarizes those options for review, shown in Figure 8. If the import settings are correct, click on Finish to start the restore operation.
Figure 8. Import Data-tier Application Introduction Screen
The restore operation will execute over a number of steps. Generally, the process will include the following steps:
- Download the bacpac file
- Create the database
- Generate the database objects via the schema
- Disable all indexes
- Import the data for each table
- Rebuild all indexes
With each step, the wizard will report either success or failure, as shown in figure 9. As the restore progresses, the screen will report each status.
Figure 9. Import Data-tier Application Introduction Screen
Summary
At the conclusion of the wizard, the database is restored. As simple as this post makes the restore appear is the actuality in the difficulty in the restore process. The only thing I’ve found lacking in the restore is the option to script out the process to allow some automation of the restore, but maybe there are PowerShell commands that could do that. As you look forward to backups and restores between on-premise and local databases, be sure to understand that while terminology may change, the world of database management is not getting that much more difficult. In fact, its getting just a little bit easier.
