My Pluralsight course for new SQL Server DBAs
This is an intro level post, specifically written for the new and accidental DBAs that have been told to direct their SQL Server backups to Azure storage…but without any additional information. It does not include discussions around backup types, recovery models, etc. Those are all great topics that are very well documented elsewhere. Also useful for veteran DBAs (like me) that suddenly have to sort out cloud technology.
Enough of that…
If you like to watch videos to learn new technology, I offer this. Otherwise scroll on down for text and screenshots, with code samples:
Basic terminology:
- Azure – Microsoft’s Cloud offering. We aren’t talking about AWS or Google today.
- Subscription – an account to create and use Azure resources
- Resource Group – a logical container for grouping like-purpose resources
- Resource – anything in Azure you can create, such as SQL Databases, Virtual Machines, Storage accounts, etc.
- Storage account – loosely similar to a Fileshare…created to hold a specific type of items
- Container – a sub-group within a storage account – roughly similar to a folder on a fileshare
- Access key – a code you need in order to access a Storage account
- URL – the location specific to the container you have created
- SQL Credential – created at the SQL instance – uses the access key to allow backups to go into containers. The credential is specified in a backup or restore statement.
What we are going to do:
- Create a storage account of Kind “Storage”
- Create a container – “Blob Service”
- Create a SQL Server credential
- Create a new SQL Database on my 2016 instance
- Backup the database to the new container
- Restore that backup to a new database name
- All of this in T-SQL, because the SSMS GUI is cumbersome
Assumptions:
- You already have an Azure account/subscription
- You already have a test SQL instance, SQL 2012, SP1, CU2 or higher.
- You are an amazing and wonderful person
- You know the basics of SQL Server backups and recovery models
- You are going to follow me on Twitter
- You are interested in being even more awesome at your job than you already are
Start with the Azure part:
Log into your subscription and click Storage accounts on the left side, then click +Add:
Select the properties for your new storage account.
Account Kind: Storage or StorageV2, not Blob Storage
Performance: Standard vs. Premium is spinning disks vs. SSD…Standard for testing or infrequently accessed files.
Replication: research and choose wisely
Resource group: where will this storage account go? Just a logical container
Location: probably a good idea to keep it fairly close to the SQL Servers you are backing up for performance reasons…but you may want to send it farther away…your call.
Click Create and wait for the notification to let you know the account exists.
Go back to the storage accounts blade and refresh to see your new account. Click it.
Verify the settings you chose are there (highlights below):
Click on ‘Access Keys’ to see the two auto-generated Keys and Connection strings for accessing this storage account. Do not give these out freely…only to those with a need for them. If they get out, you can regenerate either one or both pairs. You will need one of the 2 keys when we create a SQL Server Credential later on.
Click ‘Containers’ under Blob Service
Click + Container and give it a name. Names can only be lowercase letters, numbers and hyphens. If you enter an illegal name it will not let you continue. I use the SQL instance name here, just as I would for a regular folder on a files hare. Also, choose Private for the Public Access Level. Click OK to create the container.
Enter the name here:
The deployment of the container should be very quick. Click on its name to open it:
Note the “no blobs found” in the container. After a successful backup, you will see it here.
Click on ‘Container Properties’ to get the URL for this specific container…this will be used in Backup and Restore statements. Click the button next to the URL to copy it. For now just remember where this is or copy it to Notepad, Query window etc. When we start to build our T-SQL statements, we will need both the Access key from earlier and the URL.
At this point, you have an Azure Subscription, with a Standard Storage Account, that has a Blob Container in it. For now, that is all we will do in the portal, but leave your browser open for copying the Key and URL, as well as refreshing to see results of the Backup command.
Now for the SQL Server part:
Open SQL Server Management Studio. I am using 16.x despite 17.4 being out as of this writing, mostly because I upgrade slowly and don’t like the icons of the 17.x releases. I am using SQL Server 2016 Developer Edition on a Windows 10 Pro Dell Precision laptop.
Create a SQL Server Credential
Note that I have no Credentials at this time:
Go to your portal, and copy one of the Access Keys associated with your new Storage Account.
USE master GO CREATE CREDENTIAL SQLBackups --give this a meaningful name --storage account name: WITH IDENTITY='sqlbackups12345', --storage account key from portal: SECRET = 'pvv99UFQvuLadBEb7ClZhRsf9zE8/OA9B9E2ZV2kuoDXu7hy0YA5OTgr89tEAqZygH+3ckJQzk8a4+mpmjN7Lg==' GO
Run this in a Query Window, using the Storage Account name and Key from your subscription.
Refresh the Credentials in SSMS and verify the new one was created:
If you don’t already have database you want to backup (you should be doing this on a test system…), create one:
-- Create a test database -- minimal options for demo...don't create -- your databases like this Create Database Test
Now, the whole point of all this…create a Backup Database command:
The ‘TO URL’ replaces the ‘TO DISK’ you are used to. It includes the URL from the portal for the container as well as the name of the file you are creating. Also, the WITH CREDENTIAL is new:
--back it up to Azure --get URL from portal, add database name-date to the end of the URL BACKUP DATABASE Test TO URL = N'https://sqlbackups12345.blob.core.windows.net/kbh-precision-2016/Test_20180114_1038am.bak' WITH credential = 'SQLBackups'; GO -- go see the file in the portal
If your Backup created successfully, go to the Container in the Portal and refresh:
To RESTORE the database from the Blob backup you just created, use the same URL and Credential:
-- Restore the DB to a new DB: --use the same URL as above -- WITH Moves to new file names RESTORE DATABASE Test_restored --new database name FROM URL = 'https://sqlbackups12345.blob.core.windows.net/kbh-precision-2016/Test_20180114_1038am.bak' WITH CREDENTIAL = 'SQLBackups', Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Test_Restored.mdf', Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Test_Restored.ldf' ; GO
And refresh the SSMS Database list:
That’s it…the basics and minimums. You can of course add other normal Options, such as STATS and COMPRESSION.
Edit…when I wrote this, I was not aware of a decent way to clear out files beyond a certain number of days.
Messing around with a PowerShell script I found, I got this:
#Script to delete backup files # Set variables $container="yourcontainername" $StorageAccountName="YourStorageAccountName" $StorageAccountKey="YourStorageAccountAccessKey" $context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey $retentiondate = Get-Date # read the list of files from the container $filelist = Get-AzureStorageBlob -Container $container -Context $context #-Blob *trn foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt (($retentiondate.ToUniversalTime().AddDays(-7)))}) { $removefile = $file.Name if ($removefile -ne $null) { Write-Host "Removing file $removefile" Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context } }
Works on a VERY limited test on my system. You can change the retention in the AddDays. I had to add .ToUniversalTime to mine as PowerShell was reading the blobs as UTC/zulu and not deleting based on US Central.
If you have any other questions, feel free to ask in the comments. If you get specific errors in the BACKUP or RESTORE commands, head off to Google first is your fastest choice.
Edit 2: Don’t use the VERIFY option if you are backing up to Azure directly…read this on why that can be an expensive option
Thanks for reading!
What about deleting backups in the storage account that are over a certain age?
Does that require a powershell script? Can you post it?
Excellent question Gil…I’m not there yet. I have no doubt PowerShell can handle it, and the dbatools.io team may already have that, but I haven’t looked. I also want to test to see if OLA has that built into his script. Either solution will need the SQL Credential or Storage Key, the URL and the file name (to complete the path), presumably from the backup tables in msdb
There is a script here from Microsoft that you can test…
https://blogs.msdn.microsoft.com/sqlserverfaq/2017/01/22/automate-sql-server-backup-file-removaldeletion-from-azure-blob-storage/
Thanks a lot! After searching a day(BOL) i got your perfect steps to have backups on Azure.
Can you help me out to learn more about azure as i’m new to Azure, i’d like to read more from you. Please provide me direction to start Azure.
Keep Writing..Thanks for all your efforts !!!
Thank you for the kind words Prathamesh! I am still new in learning Azure myself. Some areas I am strong, others no knowledge at all. I do recommend looking for courses on Pluralsight and others. some may be free. Also, sign up for a free account and test things.
–Kevin
Hi Sir,
I’ m following every steps you did. However, I encountered the same error that you had when you are backing up the database to the Azure Storage. Can you give what work around you did to make it work?. Thanks!
What exact error message are you seeing? I don’t recall what error I ran into other than perhaps permissions.
Hi Kevin (or is it Barrack Obama? ) ;-),
Fantastically easy to follow video…many thanks!
Do you have any videos to follow for turning these one off backups into weekly ‘append able’ backups using the extra option like ‘FORMAT’ etc?
Many thanks
Neil
I don’t follow you on the Obama reference. Moving on…isn’t FORMAT largely used to wipe out a Tape? I didn’t play around much with the extra options but you certainly should…and report back what you discover!
Most of my posts are done intentionally with minimal options that people need to get something to work, that they can then expand on in their own testing.
I need to restore a database of 1 tb from azure portal to new db using Azure portal..can we do it using same scenario
Hi Siva,
I’m not aware of anything specifically in the Azure portal about restoring databases. You don’t mention where the backup is, what format it is or what type of SQL Server (IaaS, on-prem< Azure SQL DB) you are trying to restore to, so I can't help any.
Hi! Thanks for this useful post.
I tried to follow your steps, and, like in your video, I obtain error 400: invalid request.
How can I do?
Thanks in advance.
https://docs.microsoft.com/en-us/troubleshoot/sql/admin/nonrecoverable-io-error-backup-url
This is best I have…3 year old post. I barely remember recording it, plus a lot has changed in Azure 🙂
Hi Ruben
I changed TSLversion to 1.0 in the storage properties.