Support

If you have any questions, need help using the service or want to report a problem please feel free to contact us at:


Azure SQL Database backup works by exporting your Azure SQL Database to a .bacpac file. The exact permissions required for exporting a database from Azure SQL Database are not currently documented by Microsoft but it is believed that either the service administrator account or a co-administrator account is required. See this MSDN forum post for more details.

Alternative solution

A user has reported using the following permissions with success and I have confirmed this:

  1. Using the server management account, create a login for the backup process on the master database. Note you need to open a separate query window for this as Azure SQL Database does not support the USE statement.

    CREATE LOGIN MyBackupUser WITH password='myP@ssword'

  2. Cherry Safe Cloud Services connects through the default connection to the server. The backup user needs to exist on the master database so use the same query window as above.

    CREATE USER MyBackupUser FROM LOGIN MyBackupUser

  3. On the database you want to back up, create the backup user.

    CREATE USER MyBackupUser FROM LOGIN MyBackupUser

  4. Add the roles db_backupoperator and db_datareader to the backup user.

    EXEC sp_addrolemember 'db_backupoperator', 'MyBackupUser'
    EXEC sp_addrolemember 'db_datareader, 'MyBackupUser'

  5. Grant view permissions for DATABASE STATE and DEFINITION to the backup user.

    GRANT VIEW DATABASE STATE TO MyBackupUser
    GRANT VIEW DEFINITION TO MyBackupUser

To back up or restore SQL Server 2012 databases, Service Pack 1 and cumulative update 2 must be installed.

Permissions

  • To back up SQL Server databases, the user must be a member of the db_backupoperator database role with Alter any credential server permissions.

    USE master
    CREATE LOGIN MyBackupUser WITH PASSWORD='myP@ssword'
    GRANT ALTER ANY CREDENTIAL TO MyBackupUser
    
    USE WidgetDev
    CREATE USER MyBackupUser FROM LOGIN MyBackupUser
    EXEC sys.sp_addrolemember 'db_backupoperator', 'MyBackupUser'

  • To restore a SQL Server database to a new database, the user must have Alter any credential and Create any database server permissions.

    USE master
    CREATE LOGIN MyRestoreUser WITH PASSWORD='myP@ssword'
    GRANT ALTER ANY CREDENTIAL TO MyRestoreUser
    GRANT CREATE ANY DATABASE TO MyRestoreUser

  • To restore a SQL Server database to an existing database, the user must have Alter any credential and Create any database server permissions and be either the database owner (dbo) or a member of the sysadmin server role.

    -- To overwrite existing...
    
    USE WidgetDev
    EXEC sys.sp_changedbowner 'MyRestoreUser'
    
    -- or
    
    EXEC sys.sp_addsrvrolemember 'MyRestoreUser', 'sysadmin'

For more information about the requirements for backing up to and restoring from Azure blob storage, see MSDN.

To take a transactionally consistent backup the system performs the following steps on your behalf:

  1. Creates a copy of the database. (Microsoft charges databases per hour)
  2. Backs up the copy of the database.
  3. Once the backup has completed, drops the temporary copy.

In order to copy a database, the login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy the database to another database.

MSDN provides more information about copying your databases:

In order to copy a database in Azure SQL Database, your login requires the following permissions:

The login must be a member of the server-level dbmanager role. Note: the server-level principal of your Azure SQL Database server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in Azure SQL Database, see SQL Database Authentication and Authorization: Granting Access.

Storage

Each backup you store you will incur storage charges from Microsoft or Amazon, in accordance with its size. You can view charges at:

When backing up Azure to Azure it doesn't matter which data center you are in the data will be copied using the Azure Blob Copy API. So if you're copying data between storage accounts in the same data center the most you'll just be charged with the ListBlobs calls required to compare and confirm the files have been copied.

When backing up from Azure to S3 data egress is charged for all files that are copied. And conversely the same applies when copying from S3 to Azure.

Azure table

Currently the system runs in East US and West Europe so data egress will be charged for the data of the table if it's not in one of those data centers. However since the update to a more distributed system there are plans to put systems in data centers to remove this data egress cost.

Generally the system will attempt to run in the target data center so if you have a table in East US backing up to West Europe you can expect to pay egress charges for the backup.

Azure SQL Database

If you choose to perform a backup that is "transactionally consistent" you will be charged by Microsoft for an additional database at an hourly rate. This is detailed on the SQL Database Pricing page.

A single subscription of $10 per month entitles you to back up or restore an Azure SQL database, Azure tables, Azure containers or S3 buckets up to 31 times. To back up or restore more items or on a more frequent schedule, you will need additional subscriptions. To calculate how many subscriptions you need or for details of bulk pricing, see the pricing page.

You can increase or reduce your subscription level at any time by logging into your account and going to Settings > Manage subscription. If you attempt to run more jobs than your subscription level allows, your jobs will work but you will be reminded by email to increase your subscription level. This allows you to test features without compromising your existing jobs.

Subscription modification

Credentials are encrypted using AES with a random 192-bit key. The key is then encrypted with a 1024-bit RSA certificate, and stored in our database. The RSA certificate is stored separately from the database.

The backup is a Microsoft .bacpac file. This is a logical backup file format that contains database schema and data.

You can investigate this backup as it is a renamed .zip file with an XML representation of the database schema and a JSON representation of the data in each table. The detailed specification of the format can be found on MSDN

Due to differences in the names allowed for metadata in S3 vs Azure when copying from S3 transformations occur. In S3 the names are very permissive but in Azure the names must be a valid c# identifier.

So for example an S3 key "x-amz-meta-md5-hash" would be tranformed into "md5_hash" for the Azure metadata. Generally invalid characters are replaced with an underscore. There is also a special-case to allow metadata keys that start with a number so "x-amz-meta-12monkeys" becomes "_12monkeys".

This is operation is not reversed when copying from Azure to S3 as all keynames in Azure are valid in S3. So if you copy from S3 to Azure and back to S3 the metadata keynames will be those that are valid in Azure.

The retention policy on the target container is stored as azure blob snapshots. Snapshots in azure blob storage are stored alongside the blobs themselves you can use tools such as the free Azure Explorer or the not free Azure Management Studio to access these blobs.

Storing retention policy files as snapshots reduced Azure costs as the files themselves are stored as block diffs against the previous version. This does mean that just browsing the storage the snapshots are apparent so other more functional tools must be used to retrieve previous versions of the file.

There's a bit of information on how snapshots works on MSDN "Understanding How Snapshots Accrue Charges".