How to Set Up Managed Backups in SQL Server 2014

Earlier this week I introduced the concept of Managed Backups (and caused less of a turmoil than I thought I would). Now I want to show you how it works. It’s really simple and quite well documented. Before you get to the, insanely simple, task of actually enabling Managed Backup, you will need to go through the prerequisites. First, and this should be obvious, but I’ll state it, just in case, you need to set up an Azure storage account. That’s so insanely straight forward that I’m not going to say more. Then, you have to set up encryption on your system. I used these commands to prep it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD =
    '$qlserver2012queryperformancetuning';

CREATE CERTIFICATE CloudDojoCert
WITH SUBJECT = 'Backup Encryption Certificate';

Again, shouldn’t have to say this, but covering my bases, don’t lose that password. You might need it later. You’ll also need to create a credential (I had one created for Backup to URL already):

CREATE CREDENTIAL MyCredentialName
WITH IDENTITY = 'MyStorageAccountName',
SECRET = 'MyAccessKey';

The actual command to get the backups started is crazy simple:

EXEC smart_admin.sp_set_db_backup @database_name = 'MyDatabase',
    @retention_days = 10, @credential_name = 'CloudDojo',
    @encryption_algorithm = 'AES_128', @encryptor_type = 'Certificate',
    @Encryptor_name = 'CloudDojoCert', @enable_backup = 1;

That’s it. By setting @enable_backup to 1, I’m and running. I can repeat the script for multiple databases and I’m done. Backups are now automatic. There’s auditing built in. For example, I seem to get errors on RESTORE HEADER_ONLY occasionally:

DECLARE @eventresult TABLE
    (
     event_type NVARCHAR(512),
     event NVARCHAR(512),
     timestamp DATETIME
    )

INSERT  INTO @eventresult
        EXEC smart_admin.sp_get_backup_diagnostics @begin_time = '12/1/2013',
            @end_time = '12/19/2013';

SELECT  *
FROM    @eventresult
WHERE   event_type LIKE '%admin%';

errors

[FileRetentionAdminXevent] Type = SqlError, Code = 3292, Stage = Undefined, Message = A failure occurred while attempting to execute Backup or Restore with a URL device specified. Consult the Windows Event Log for details.
RESTORE HEADERONLY is terminating abnormally., Additional Info = N/A.

Browsing through the files (using Cerebrata because it makes my life so much easier), I can see that it’s an intermittent problem, not a consistent one. And, since it’s just the verification of the backup, I’m not sweating it that much:

files

It’s the kind of intermittent error you’re likely to get when working with cloud-based technology. Code accordingly.

But that’s it. It’s so easy to get set up. You can see that I’ve been running backups for days. Managed Backups are easy to set up. They’re easy to turn on. You can use RESTORE FROM URL to pull these back into your database just as easily as I put them out there. There’s really nothing to it.

Again, this is not for everyone. It’s just another tool in the toolbox. Yet another way of getting things done. Explore it. Let me know what you think.

2 thoughts on “How to Set Up Managed Backups in SQL Server 2014

  • Hi Grant,

    I really like this new feature. It’s easy to implement and like you mention, not for everybody but really useful for smaller databases. I’m still trying to find out how it works because in BOL they’re mentioning it’s handled by the SQL Agent, but I can’t find any jobs running. What I noticed is that a check is done every 15 minutes to see if a backup has to be taken or not.

    I hope that they improve this functionality a bit more like adding a GUI on top of it and make it possible to tune it a little bit. I hope they will make it available for system databases and on-premise backups.

    Regards
    Pieter

  • I would also like it to work with on-premises backups. But it’s a pretty nice little addition to the toolbox. Nice to see others appreciate it too. I don’t think it uses Agent. I’m pretty sure all the scheduling is from the DLL. I don’t have any jobs AND my agent was disabled, but it was running anyway.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.