SQL Tip-12 | Using SQL Server Backup to Windows Azure Tool – Part2

In last part we discussed the installation, configuration and usage of the SQL Server Backup to windows azure tool.

In this part we’ll discuss some internals of this tool and how to restore the backups which were moved to Azure storage.

As we discussed in the last part this tool creates a login ID with the name of SQLBackup2Azure , let’s diagnose it further. The tool creates this login with a low privilege account which is used to run this service. This tool also creates a group called “TempGroup” and the login SQLBackup2Azure gets the membership of this group. The name of the group and login are hardcoded and can’t be changed. However the password of the login gets generated automatically and randomly which is not known to anyone including Microsoft.

When you start adding different rules in this tool, this group get read\write access on the specified location – that is how this tool is able to take backup and restore.

The biggest disadvantage of this tool is it doesn’t provide a mechanism to track the uploaded files and any kind of error logging information, you don’t even have any control over the schedule of the uploading of backups since there is a service it keeps on checking the files.

 

Restore a Database from the Backup file which was uploaded using this tool

This tool creates a Stub file with some metadata to use during restore. You can use this tool just like a regular backup file when you want to restore a database. SQL Server automatically reads the metadata information in the file and restore the database by getting the required file from the Azure Account. Internet connectivity is a must since the stub file contains only the metadata information about the backup file rest everything is there in the Azure Storage account.

 

Stub File

This tool uploads the specified backups from the file path mentioned in the rule but once the backup file gets uploaded it creates a Stub file for the same and leaves it in the local storage folder with metadata information to be used during restore.

 

What if Stub file is inaccessible or Deleted by mistake?

A copy of the same Stub file gets uploaded along with the backup on your Azure Storage Account. If for any reason the stub file is inaccessible you can download the copy of the stub file and place it in the same location which is configured for this tool.

 

Some known issues:

1. In the event of a loss of network connectivity, SQL Server will surface the error as not being able to write to the device. You will need to clean up the files relating to this backup operation (if any) and retry the backup.

2. If you want to create a backup using the Tool, you must create a new backup file instead of overwriting/appending to an existing backup file.

 

Thanks,
Sarabpreet Singh