SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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:


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):

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';

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


[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:


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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...