en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
נוב23

Written by: ronen ariely
23/11/2017 10:22 RssIcon

SQL Server Backup and Restore to Azure Blob Storage Service

This is a simple Step-By-Step tutorial, showing how to use Azure BLOB Storage services to store backups of SQL Server databases. During the tutorial I will start with creating a new Azure Storage Account, next I will create new Credential in our local SQL Server and using the Credential I will backup and restore the SQL Server database.

Create Azure Storage Account

https://docs.microsoft.com/en-us/azure/storage/common/storage-create-storage-account

1.1.     Sign in to the Azure portal.

1.2.     Navigate to “Storage accounts” (you can use the search box)

1.3.     Click on “Add”

1.4.     Fill the form:

1.4.1.  Name:

1.4.2.  Deployment model: Resource Manager

1.4.3.  Account kind: Storage (general purpose v2)

1.4.4.  Performance: Standard

1.4.5.  Replication: Locally-redundant storage (LRS)

1.4.6.  Access tier (default): Cool

1.4.7.  Secure transfer required: Disable (for testing and learning)

1.4.8.  Subscription: Select the subscription which you want to use 

1.4.11.      Virtual networks: Disable

1.5.     Click on Create

1.6.     Once the storage is created, get account key

1.6.1.  Navigate to the new storage service using the portal

1.6.2.  In the left menu under setting, click on “Access keys”

1.6.3.  Copy the storage account name and the key (we will use these next step

1.7.     Create new container

1.7.1.  Navigate to the new storage service using the portal

1.7.2.  Click on adding add container, fill the name of the container and select private (no anonymous access). Remember the container name for next steps

Create a SQL Server Credential

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql

Execute the following query using your storage account name and the key, which you got in previous step (1.6.3)

CREATE CREDENTIAL Your_Credential_Name
    WITH IDENTITY = '<Your storage name>',
    SECRET = '<Your storage key>'
GO

 

Backup Databases to the Azure blob

Execute the following query using your personal information according to previous steps

BACKUP DATABASE DatabaseName
WITH CREDENTIAL = 'Your_Credential_Name';
GO

 

Restore Database from Azure blob

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

RESTORE DATABASE DatabaseName
WITH REPLACE, CREDENTIAL = 'Your_Credential_Name'
GO

 

Note! Using the parameter REPLACE we override the existing database and it will replace it. Do not use this if you need the current database.

Conclusion

This is a simple clean demo we learned how to use Azure Storage Account as a storage solution for SQL Server backups. We show hoe to backup databases and restore them directly from/to the Azure storage using simple T-SQL scripts.

* The entire tutorial is for learning purpose only, and it should not be used in production as it is.

** update: Instead of using Azure Storage BLOB and URL, we can also use Azure File which can be configure as simple shared folder.

I hope this was useful and I’ll see you on Facebook and in my next blog post