With SQL Server 2014, we are able to back up a database to a URL via SSMS. In fact, this feature came with SQL Server 2012 SP1 CU2, but could not be done through SSMS.
Step1 (Create an Azure account)
You will need to have an azure account. You must first create an azure account at https://portal.azure.com/.
Step2 (Create a Storage Account on Azure)
Click on Storage accounts on the left side of the screen you see in the picture below. On the screen that appears, we click Create Storage Accounts as below.
On the screen that appears, we perform the following operations from the Project details section.
Note: You can select Blob from Account kind.
On the next screen, click Create to create the Storage Account.
After creating our Storage Account, we click Storage Accounts again on the left side and click on the storage account we created from the screen below.
Then click on Blobs.
On the screen that appears, click the + sign next to the container to create a new container as follows.
Then we create our container as follows.
After creating the container, we click the container, then click Properties and note the following URL.
Step3 (Create SAS Token To Create a Credential)
Go back to the main screen of the storage account we created with the name mynewtestaccount and click Shared access signature and “Generate SAS and connection string” as shown below.
Then copy the SAS token. We will use this code later.
Important note: Copy the SAS token without “?” sign. Otherwise you may receive one of below errors.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘https://mynewtestaccount.blob.core.windows.net/mytestcontainer/AdventureWorks2014.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Important note2: Note expiry date of your SAS token. Otherwise you can not backup your database to azure storage account or restore your database to your local instance from this backup.
Step 4 (Create a Credential To Connect Securely to Azure Storage Account)
We need to create a credential as follows:
CREATE CREDENTIAL [write_your_credentialname_here] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,
SECRET = 'paste_your_SAS_token_here_without_?';
My create credential script:
CREATE CREDENTIAL [https://mynewtestaccount.blob.core.windows.net/mytestcontainer] WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-09-05T16:30:56Z&st=2019-09-05T08:30:56Z&spr=https&sig=VnUmMllk4Xys1KDi6CdXMQJWQ9NH7AQsRWifavkgNxI%3D';
Step 5 (Create a Backup To Azure URL)
Finally we can create a backup on our azure blob storage. You can find the backup script below.
BACKUP DATABASE [AdventureWorks2014]
TO URL = N'https://mynewtestaccount.blob.core.windows.net/mytestcontainer/AdventureWorks2014.bak'
WITH COMPRESSION, STATS = 1;
Step 6 (Check and Access to Backup on Azure)
Once the backup is complete, we click Storage accounts and then our storage account named mynewtestaccount as follows.
Then Click Blobs.
Finally, click container named mytestcontainer.
Then you can see our backups in this path.
Step7 (Connect Azure Storage From SSMS)
In addition, you can connect to Azure Blob Storage from SSMS:
To connect to Azure Blob Storage from SSMS, Click Connect on Object Explorer and Azure Storage as follows.
Write your storage account name to Storage account and key1 value to account key and click Connect.
You can find Storage account name and key1 value by clicking Acces keys after clicking your storage account.
You can find the example below.
You can see the backups on the azure storage from SSMS like below.
If you want to restore a backup in Azure storage to the local instance, I recommend you to read the article “How To Restore a Backup From URL(Backup On Azure Storage)“.