How to Backup SQL to Azure Storage

SQL Server 2016 allows you to backup a database to Azure Storage. I found the information about this to be disjoint at best. So, if you want to do an old style (non-snapshot) backup of a database this is how I achieved it;

Background

My Server is running on a Virtual Machine in on the same Azure Resource Group as the storage account, I’m not too concerned about SQL knowing the storage account keys, so I’m going to show the shortest path to getting this working

Steps

Assume we’re backing up a Database called Products.

  1. Create Storage Account – Create a ‘standard’ non premium storage account. Don’t be tempted to use premium, it will not work. Let’s call it sqlnoprembackup
  2. Create a container in the blob storage, perhaps ‘backupsql1’. Grab the full path to the container, if you’re using the portal select the container and view the properties. In this example it is; ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1
  3. Grab the account name and key for the storage account (Access Keys if you’re using the portal). Here it is sqlnoprembackup and the first long key.
  4. Create the certificate on SQL Server, let’s call it backupstorage;
  5. IF NOT EXISTS 
    (SELECT * FROM sys.credentials  
    WHERE name = ‘backupstorage’) 
    CREATE CREDENTIAL [backupstorage] WITH IDENTITY = ‘sqlnoprembackup’ 
    ,SECRET = ‘<insert secret storage key from (3) here>’; 

  6. Backup the database, use the full path to the container from (2). Note stats=5 will display the % progress at 5% internals in the messages tab;
  7. BACKUP DATABASE Products
    TO URL = ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1/products.bak’  
    WITH CREDENTIAL = ‘backupstorage’  
         ,COMPRESSION 
         ,STATS = 5; 

  8. Run it. Yay.

    Restoring the database

    To do a full restore of the database;

  1. Take the database offline;
    ALTER DATABASE Products SET OFFLINE WITH NO_WAIT
  2. To restore the database;
  3. RESTORE DATABASE Products FROM URL = ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1/products.bak’  
    WITH CREDENTIAL = ‘backupstorage’, REPLACE, STATS=5 

    NB, take a look at Snapshot backups, it’s a much better solution. Sometimes, as in my case, it’s not available and this hybrid approach works quite nicely.
    Advertisements
    Posted in SQL Server, Azure | Tagged | Leave a comment

    SQL Server – The log cannot be rebuilt because

    I had the blood-drawn-from-face moments when a database stated is was ‘pending recovery’. Ultimately the transaction log file was broken. Actually I didn’t care about that because I knew it was pretty much all read-only data. So how to recover it? I tried various recovery techniques but the server was adamant, ‘restore from backup’. Well, I couldn’t do that either. So finally I found this little nugget of DDL, create a new database using the existing data files but with a new transaction log;
    CREATE DATABASE [<new dbname>]

    ON  PRIMARY
    ( NAME = N'<filename>’, FILENAME = N'<path to>.mdf’  ),
    ( NAME = N'<filename2>’, FILENAME = N'<path to 2nd>.mdf’ )

    FOR ATTACH_REBUILD_LOG
    Posted in SQL Server, Uncategorized | Leave a comment

    Azure Table Insert, one of the request inputs is out of range

    Whilst trying to insert a new row via a TableEntity derived class I hit a strange error;

    ‘one of the request inputs is out of range’

    Looking at my object it all seemed fine to me. The problem was that I had a DateTime property that I had not set. Although it was defaulting to the minimum date time Azure did not enjoy that. So I simply ensure that date property has a reasonable value in it and problem solved.

     

     

    Posted in Uncategorized | Leave a comment

    Surface Phone…what if

    I’ve no insight into what is in or out of the Microsoft Surface Phone or if it even exists. However, I was thinking what could it realistically do to be game changing. Well how about this. When undocked you can use UWP apps. Fine, that 1% of the apps the world wants but typically enough to read your emails and surf the odd web page. When you the dock the phone is able to run win32 apps. So you really would get a “full”, if maybe slightly underpowered, laptop. That really would be a legitimate reason to own one.

    Posted in Uncategorized | Leave a comment

    Html, CSS finally come of age?

    Attended a really inspirational talk, perhaps HTML/CSS will now be about design rather than weighed down by JavaScript libraries and polyfills?

    Jen Simmons CSS Grid

     

    Posted in Uncategorized | Leave a comment

    SQL Server with external drives – pending recovery

    When you are developing a SQL Server database you might want to use External Storage devices. Beware of a couple of slight gotchas. When you restart your machine SQL Server may start before the drives become available. If this happens SQL Server will mark the database as ‘pending recovery’. To get out of this situation just restart the SQL Server instance and select ‘refresh’ the database node in SQL Management Studio. A second, related problem, is that the drives may not be assigned the same drive letter, this will again result in ‘pending recovery’. If you cannot remember the drive letter you assigned you can run the following against the system database;

    select [name], physical_name from sys.master_files

    If you now have an incorrectly assigned drive letter, the best solution is to open the disk management tool where you can right-click and assign the original drive letter back.

    Posted in SQL Server, Uncategorized | Leave a comment

    CTRL ALT DEL in remote desktop

    …is CTRL ALT END

    Posted in Uncategorized | Leave a comment