Backup SQL Server 2014 database from Windows Azure VM to Windows Azure storage

Microsoft’s hybrid cloud solution has the model like the above drawing for cloud backup and cloud disaster recovery to reduce costs and improve on-premises disaster recovery. In previous blog post we have seen how we could back up from on-premises server to Windows Azure storage. In this blog we will see how we could backup a SQL Server 2014 database from Windows Azure VM to Windows Azure Storage.

If you are not familiar with SQL Server in Windows Azure VM check out this blog first. This process will help us avoid On-site/off-site storage costs and device management costs while sitting on the VM and moving it to Windows Azure storage help us to gain nearly “bottomless” storage, off-site, geo-redundant, no provisioning, no device management, media safety (decay-free) and remote accessibility.
That being said let us we how see could backup our database in action:
For this demonstration I am going to use a virtual machine we built in this blog and a credential we created on this blog.
First thing first normally we create a credential using the storage and the secret key (check here).

--create credential--
use sql2014DB;
go
 
create credential AzureCredential
with identity = 'reckonedforce',
secret = 'AnOzW0xjQ3cIKoM23C8Y7y2vITkqd0zv1s2J9NlpuAvegAlu/9kBRwjwOmiEHGjWNIsDWusRSSbakGsm/rn84w==';
go
 
select * from sys.credentials;
go

We need to take care of two problems before we could be able to create a smart back up. In the first attempt I get an error similar to the bottom one and it just doesn’t let me get around it so I have to turn on the trace flag (10100) globally. But what is trace flag 10100 I have no clue and I can’t find anything on the web about it. I am sure MS will give us some idea what it is in the actual release so let’s agree by just turning it on and move on to the next step

-- turn on trace flag 10100 globally --
DBCC TRACEON (10100,-1)
go

Next we have to turn on sql server agent service or you will get an error similar to the one below. Use a script below or just simply turn it on manually.

-- start sql server agent service or simpley start the service manually
EXEC xp_servicecontrol N'start',N'SQLServerAGENT';
go

Let us now create the smart backup job. Notice you have to be on msdb system database.

--create smart backup--
use msdb;
go
EXEC smart_admin.sp_set_db_backup
@database_name='sql2014DB',
@storage_url='http://reckonedforce.blob.core.windows.net/backup/',
@retention_days=30,
@credential_name='AzureCredential',
@enable_backup=1;
go

Let us enable configuration on the smart backup so that we could be able to query it

--enable smart configuration
use msdb;
go
EXEC smart_admin.sp_master_switch 1;
Go

And let us check the configuration. Great we manage to set up our backup to our Windows Azue Storage with 30 day retention.

--check the smart backup configurations and files
use msdb;
go
select * from smart_admin.vw_backup_configuration;
go

Now that we finish our backup process why don’t we check if we actually start to back up to our storage from the management portal?

Perfect the backup process already created a backup and log blobs in our storage for us and this concludes our little experiment. I will do another blog soon once I manage to restore the blobs back to the vm.
Thanks for reading and please leave a comment and let me know what trace flag 10100 is if you know it so that I could update my statement.

Reference:
SQL Server 2014 Platform for Hybrid Cloud Level 300 (SQL Server 2014 CTP1 White Paper)