Lesson 9. Restore a database from Azure Storage

In this lesson, you will learn how to restore a database backup file from Azure Storage to a database, which either resides on-premises or in a virtual machine in Azure. To follow this lesson, you do not need to complete Lesson 4, 5, 6, 7, and 8.

This lesson assumes that you already completed the following steps:

• You have created a database in the source machine.

• You have created a backup of your database (.bak) in Azure Storage by using the SQL Server Backup and Restore with Azure Blob Storage Service feature. Note that you will need to create another SQL Server Credential in this step. This credential uses storage account keys.

• You have an Azure Storage account.

• You have created a container under your Azure Storage account.

• You have created a policy on a container with read, write, and list rights. You also generated a SAS key.

• You have created a SQL Server credential on your machine for Azure Storage Integration feature. Note that this credential requires a Shared Access Signature (SAS) key.

To restore a database from Azure Storage, you can follow these steps:

1. Start SQL Server Management Studio. Connect to the default instance.

2. Click New Query on the Standard Toolbar.

3. Copy and paste the following complete script to the query window. Modify the script as needed.

   Note: You run the RESTORE statement to restore the database backup (.bak) in Azure Storage to a database instance in another machine.

   
   USE master   
   GO   
   -- Create a new database to be backed up.   
   CREATE DATABASE TestDbRestoreFrom;   
   GO   
   USE TestDbRestoreFrom;   
   GO   
   CREATE TABLE Table1 (Col1 int primary key, Col2 varchar(20));   
   GO   
   INSERT INTO Table1 (Col1, Col2) VALUES (1, 'string1'), (2, 'string2');   
   GO   
   USE TestDbRestoreFrom;   
   GO   
   SELECT * from dbo.Table1;   
   GO   
   -- Create a credential to be used by SQL Server Backup and Restore with Azure -----Blob Storage Service.   
   USE master;   
   GO   
   CREATE CREDENTIAL BackupCredential    
   WITH IDENTITY= 'teststorageaccnt',   
   SECRET = 'BO1nH/lWRdnc8TGPlQIXmGLWVCoEa48suYSGiAlC73+S0TX5VXo5/LCm8qiyGCYafDg4ZsueDIV3GQ5RXHaRGw=='    
   GO   
   -- Display the newly created credential   
   SELECT * from sys.credentials   
   -- Create a backup in Azure Storage.   
   BACKUP DATABASE TestDBRestoreFrom    
   TO URL = 'https://teststorageaccnt.blob.core.windows.net/testrestorefrom/TestDBRestoreFrom.bak'    
         WITH CREDENTIAL = 'BackupCredential'    
        ,COMPRESSION   
        ,STATS = 5;   
   GO    
   -- Create a Shared Access Signature credential   
   CREATE CREDENTIAL [https://teststorageaccnt.blob.core.windows.net/testrestorefrom]   
   WITH IDENTITY='SHARED ACCESS SIGNATURE',   
   SECRET = 'sv=2012-02-12&sr=c&si=policy_resfrom&sig=EhVpzLUXjG4ThAMLmVhrnoiCt8IfmD3BsuYiMawGzxc%3D'   
   GO   
   USE master;   
   GO   
   RESTORE DATABASE TestDBRestoreFrom    
   FROM URL = 'https://teststorageaccnt.blob.core.windows.net/testrestorefrom/TestDBRestoreFrom.bak'    
   WITH    
   CREDENTIAL = 'BackupCredential',    
   REPLACE,   
   MOVE 'TestDBRestoreFrom' TO 'C:\Backup\TestDBRestoreFrom.mdf',     
   MOVE 'TestDBRestoreFrom_log' TO 'C:\Backup\TestDBRestoreFrom_log.ldf';   
   GO  
   
   

End of Tutorial: SQL Server Data Files in Azure Storage service