March 13, 2014  by admin
0
 

Microsoft sql server backup to microsoft windows azure tool

Let’s start this article by the end and summarize it in only two words: At last !
Indeed, Microsoft has finally developed a tool which permits to compress and encrypt backups on every mssql engines and icing on the cake, it is delivered for free…

 

Here is the official description of the tool:
Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.

 

backup
Without this tool, only SQL Server 2014 has native encryption support and only SQL Server 2008 or later support compression during backup. Another thing is that SQL Server 2008 supports compression in Enterprise edition only but for SQL Server 2008 R2 and later encryption is supported on Standard editions or higher (many only…).
http://www.microsoft.com/en-us/download/details.aspx?id=40740

 

 

So, even if the tool has been initially developed to take advantage of the Azure platform, you can – without any troubles – use it for your “internal” backups and combine it with the Ola Hallengren’s scripts (http://ola.hallengren.com) giving you a very robust and powerful package to backup your databases “safely”.

 

Now, it’s time for a little demonstration !

 

We are gonna backup a database with the native backup module and afterwards we will use the Microsoft sql server backup to windows azure tool:

 

— native backup module —

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE testdb TO DISK='D:\mssql_backup\testdb\testdb_without_windows.azure.tool.bck'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Processed 514272 pages for database 'testdb', file 'testdb_data' on file 1.
Processed 4 pages for database 'testdb', file 'testdb_redo' on file 1.
BACKUP DATABASE successfully processed 514276 pages in 37.245 seconds (107.874 MB/sec).

 SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 37305 ms.

 

— Microsoft sql server backup to windows azure tool —

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE testdb TO DISK='D:\mssql_backup\testdb\testdb_with_windows.azure.tool.bck'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Processed 514272 pages for database 'testdb', file 'testdb_data' on file 1.
Processed 2 pages for database 'testdb', file 'testdb_redo' on file 1.
BACKUP DATABASE successfully processed 514274 pages in 161.298 seconds (24.908 MB/sec).

 SQL Server Execution Times:
   CPU time = 219 ms,  elapsed time = 161402 ms.

 

So, first assessment, the backup takes four more time with our new module than with the native one (Please take note that I’m running the DB on a small vm with only two cores so the factor 4 must be put in perspective).

 

backup_compare

 

You have probably noticed that the backup has been renamed as .enc so, let’s have a look to the .bck file generated:

{
  "Version": 1,
  "Azure": null,
  "Local": {
    "DataFileName": "D:\\mssql_backup\\testdb\\testdb_with_windows.azure.tool.bck.enc"
  },
  "FileSize": 4213468672,
  "CompressedSize": 1161148768,
  "Trailer": "aG9J1/AabizdW3Jo10C3dHKaSjfADCKj6DRFrP19THGQkHXLT9t9tqB2VXbAB2FS3tcJF5brY3u9aDUHxPH/2U8SBzz
   /hDo1nc8R24rsaV89EDvVxOoI7zum54cWeKLAtUEhIfqTrDtPP6fihGVYFpL4A6CZS19OmhGKlVfbXvXnDd3Sdh3ZPkfhPGkRJBSfs
   ...
   /4J2PbPF7OeAjDxpCGuY4eJT6lkamB2TjpnEVwxwSh6s1wXHQHmqGCcmawQN53DIMH0suZjNV4zYbykktXhFp6Jl9cmD6hFyxCie",
  "Compressed": true,
  "DataEncryptionDetails": {
    "Salt": "kyj7AKlYszNpd+b3f+A0tQU6RCI/HjS2pOiFL1gL7V0=",
    "Iv": "1ok88CPCd5kqQi07zOZiiA==",
    "Mac": "lyXnNXRLAUJRbOwOlR+UonfQiwvF9pKH/k/EP1SoTo0="
  },
  "TrailerEncryptionDetails": {
    "Salt": "WY7d8efwc3RAKds/iYrFc+ZHt4DB1u695le90HeTC9k=",
    "Iv": "MA2BpZA/Che05xK8SkvtGw==",
    "Mac": "hzlRtbG5bkxi++T5Fo6jJ7ovmrUCVKO+dxoelmy5yDw="
  }
}

As we might expect, the file contains the metadata related to the backup itself (datafile name, encryption key, …).

 

Now, let’s talk about the restore.

 

Initially, I tried to restore the file ” as usual” and I didn’t have any troubles.
Afterwards, I tried – once again – to restore the backup but this time, after having stopped the “Microsoft SQL Server Backup to Windows Azure Tool” service and by pointing on the bck and .bck.enc files.

 

In both cases, I’ve got the (expected) following error message:

Msg 3241, Level 16, State 0, Line 1
The media family on device 'D:\mssql_backup\testdb\testdb_with_windows.azure.tool.bck' is incorrectly formed. 
SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

 

So, here we are… What can I say to close this article except that Microsoft has delivered – for free – a great tool that many DBAs were expected for years ! So finally, what are you waiting for to use it ?

 

Oh, just before to leave, in case you would get a Operating system error 31(A device attached to the system is not functioning during a backup, this is simply due to the fact that the service used by our tool (Microsoft SQL Server backup to windows Azure tool) runs as “.\SQLBackup2Azure” and this account probably doesn’t have the access to your backup folder 😀

 

Leave a comment

Please wait......