Because so many people have asked me for specific details on backing up a SQL Server database to Azure, this post shows the necessary steps to accomplish that. The original database name is MIMIC II
, which is a clinical database that is available to researchers. It will be used in subsequent posts on data migration and machine learning. The original copy of MIMIC II is in a PostgreSQL database in an Ubuntu virtual machine. I used the SQL Server Import/Export Wizard to import the data into SQL Server, which I find more convenient to work with. I gave the database the name MIMIC2
in SQL Server 2014. When I completed the migration to SQL Server, of course I wanted a backup. Azure was my first choice. By storing it in Azure, I know it is both secure and accessible to me should I ever need it.
SQL Server 2014 is shown in the screen captures below. SQL Server versions as far back as SQL Server 2005 can be easily backed up to Azure by using the free Microsoft SQL Server Backup to Microsoft Azure Tool.
The first step is to create a storage account in Azure. On the left hand side of the page, select STORAGE. Next click either + NEW or CREATE A STORAGE ACCOUNT.
Figure 1. Pick either of the options to create a storage account.
Specify the first part of the URL that will be used to access the storage account.
Figure 2. Entering mimic2 for the URL makes the entire URL mimic2.core.windows.net
Figure 3. Click on MANAGE ACCESS KEYS.
Figure 4. You must save the access keys. They are your passwords to the storage account.
You’ll need to create a security credential to use in SSMS. Here is what your key maps to in T-SQL:
Figure 5. The storage account name maps to IDENTITY. The primary access key maps to SECRET.
Figure 6. You can use the SSMS gui to create the security credential instead of T-SQL. Notice the primary access key from Azure maps to Password on the New Credential box.
Now that you have a storage account, the next step is to create a container. Click on CONTAINERS to change the view.
Figure 7. Click on CONTAINERS to create a container for your backup.
To create the container, click either + ADD or CREATE A STORAGE ACCOUNT
Figure 8. Pick either of the options to create a container within your storage account.
Figure 9. Enter a name for your new container.
Figure 10. Your backups will reside in the blob container you’ve created.
Refer back to Figure 5 or Figure 6 to create a security credential. You’ll have to do that before proceeding to the next step.
Once you have a security credential created, you are ready to back up to Azure.
Figure 11. In SQL Server 2014, back up to Azure is accessed like any other back up method.
To backup to Azure, select URL from the Back up to dropdown list and select your credential name from the SQL credential dropdown list,
Figure 12. Select URL and your credential name from the dropdown lists.
You probably want to select Backup Options so that you can select Compress backup from the dropdown list. A compressed backup will take less time to upload to Azure and it will occupy less storage space.
Figure 13. Selecting Compress backup will reduce the upload time and Azure storage space.
You can also connect to Azure directly from SSMS and browse your Azure storage.
Figure 14. Select Azure Storage to connect to Azure to look at your Azure storage from within SSMS. The Account key is the primary access key in Azure, the SECRET in T-SQL, or the Password on the New Credential box in SSMS.
Figure 15. View your Azure storage accounts, containers, and backups from within SSMS.
Figure 16. You can also examine the contents of your container from within the Azure portal.