locked
SQL Bakcup solution on Azure RRS feed

  • Question

  • 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

    Monday, October 28, 2013 11:54 AM

Answers

All replies

  • 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

    • Proposed as answer by Cotega Monitoring Monday, October 28, 2013 5:24 PM
    • Marked as answer by Fanny Liu Tuesday, November 5, 2013 1:23 AM
    Monday, October 28, 2013 5:24 PM
  • 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


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by Fanny Liu Tuesday, November 5, 2013 1:23 AM
    Tuesday, October 29, 2013 2:08 AM
  • 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.


    Sandeep Bhutani

    Wednesday, November 6, 2013 7:46 AM
  • 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


    Your Name
    TechNet Community Support

    Wednesday, November 6, 2013 8:24 AM
  • 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.


    - Sandeep Bhutani

    Wednesday, November 6, 2013 8:54 AM