Question

Sandeep Bhutani on Mon, 28 Oct 2013 11:54:44


Hi,

I am looking for a database backup solution on azure but facing problem at following:

1. With Azure SQL Database I am not able to create a copy of database (using CREATE DATABASE Database1B AS COPY OF Database1A). It needs access to master database and in sql azure databases I dont have access to master database.

2. If create a maintenance plan for backup, that does not store .bak file on azure blob.

3. BACPAC is not a backup solution as it does not contain transactional history or incremental backups.

4. bcp is too manual.

Is there any way I can take backup of SQL Azure database and SQL installed in VM as bak file on Azure blobs?


- Sandeep Bhutani


Sponsored



Replies

Cotega Monitoring on Mon, 28 Oct 2013 17:24:15


Hi Sandeep,

There are actually a lot of alternate options for doing backups for SQL Azure.  This is a really great overview page that might be a good starting point for you:

http://blogs.msdn.com/b/davidmcg/archive/2011/09/29/data-backup-strategies-for-windows-and-sql-azure.aspx

Hope that helps!

-------------------------------------------

Cotega - SQL Azure Monitoring and Scheduling Service

Fanny Liu on Tue, 29 Oct 2013 02:08:41


Is there any way I can take backup of SQL Azure database and SQL installed in VM as bak file on Azure blobs?


- Sandeep Bhutani

Hi Sandeep,

SQL Azure doesn't support Backup and Restore which we using in the SQL Server database. You can try to use the workarounds as Cotega post to backup SQL Azure database.
If you want to store .bak file on Windows Azure storage, you can try to migrate SQL Azure database to SQL Server database. For example, export the SQL Azure database to Azure Storage which store as .bacpac file; and then create a database from the .bacpac file on the SQL Server instance with Import Data-tier Application Wizard; backup the database and store the .bak file to Azure Blob Storage.

Reference:Data-tier Applications
SQL Server Backup and Restore with Windows Azure Blob Storage Service

Regards,
Fanny Liu

SandeepBhutani on Wed, 06 Nov 2013 07:46:14


Hi Fanny,

.bacpac does not contain complete backup. It contains only schema and data (no transactions logs etc) (Please correct me if I am wrong!)..therefore it can not be considered as a backup solution in production environment.

For your comment - backup the database and store the .bak file to Azure Blob Storage. - It is not clear to me, can you explain how can this be achieved.

Fanny Liu on Wed, 06 Nov 2013 08:24:49


For your comment - backup the database and store the .bak file to Azure Blob Storage. - It is not clear to me, can you explain how can this be achieved.


Sandeep Bhutani

Hello,

Please refer to the second link as I post above to backup SQL Server database to Windows Azure Blob storage service. You can refer to the following statement:
BACKUP DATABASE AdventureWorks2012
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak'
      WITH CREDENTIAL = 'mycredential'
     ,COMPRESSION
     ,STATS = 5;
GO

Regards,
Fanny Liu

Sandeep Bhutani on Wed, 06 Nov 2013 08:54:53


Hi Fanny,

this should work on SQL server installed in a VM or on-prem sql instance.

However when using SQL Azure, I get error that I dont have permissions for BACKUP command. (I also do not have access to master database!). Is it by design or I am missing something. If by design, how can I run this command on SQL Azure database.