Dec 19 2013

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.

3 Comments

  • By Pieter Vanhove, December 22, 2013 @ 1:08 pm

    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

  • By Grant Fritchey, December 23, 2013 @ 9:46 am

    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.

  • By fifa 15 cheats coins, September 30, 2014 @ 12:13 am

    In the beginning you generally play whatever you like, later it’s useful to communicate with your teammembers
    before the match begins so that you have a balanced setup and not 5 champions of the
    same kind. What makes Shen unique is his power system – instead
    of using mana or health, he has an energy bar that is much smaller than a mana bar but regenerates much quicker.
    When playing in windowed mode, players can now interact with the game even while the options menu
    is open While playing in windowed mode your mouse cursor
    will be constrained to the game window regardless if the options menu is open or not Pressing F9
    (default hotkey) releases the mouse from the game window so you can interact with your desktop or other applications.

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment