Creating backups of user databases from an on-premise environment to Azure Blob Storage is an easy task. You need to configure Storage Account in Azure where your database backups will be sent. Then you can reference this newly created storage container directly from SQL Server Management Studio when creating a new backup. Same time you can generate T-SQL for later usage.
There are basic steps listed to accomplish this task:
1. Create a new SQL Server database that we will later backup to Azure Blob Storage. Name the database CompanyData.
2. Then open the Backup data dialog as usual. Instead of Disk choose URL option in Back up to section.
3. In the next dialog press the button New container because we haven’t created it before in Storage Account. If you already have one then you can list for it.
4. Next press Sign In in the next screen and sing in into your Azure subscription where your Storage Account was created.
5. After you have signed in choose the right subscription, Storage Account, Blob Container, and press the button Create Credential. This will create the Shared Acess Signature (SAS).
6. When you have pressed OK in the previous step you are back in Select Backup Destination screen where you can see your Storage Account container successfully attached. Press the OK button.
7. You can see that everything is ready for backup now. Press OK and the backup process will start like in backup to Disk scenario.
The Error occurred icon in the picture will occur if you will try to press the Script button. Hopefully, this will be corrected soon in the nearest SSMS release. For now, we will capture the background query via Profiler:
BACKUP DATABASE [CompanyData] TO URL = N'https://sqlpoweredbackups.blob.core.windows.net/sqlbackups/companydata_backup_2019_05_25_081859.bak' WITH NOFORMAT, NOINIT, NAME = N'CompanyData-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
8. You can use Azure Storage Explorer to check that our backup file exists in the target Azure Blob Storage container.
That’s it. Nothing complicated and very similar do Disk backup type.
You would like to where are your authentication credentials stored? In the BACKUP statement itself we have captured with Profiler there is no such reference. Your credentials were used to create the Shared Access Signature key that is located in the Credentials folder under Security parent for later use.
