backup

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!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs