locked
Scheduled backup to Azure Blob Storage RRS feed

  • Question

  • I have the SQL Server backup to blob storage working when I manaualy run T-SQL commands. I would like to setup nightly backups going to blobl storage. This cannot be done throught maintenance plan UI in SQL Server 2012. Can I use T-SQL to schedule nightly backups that backup to azure blob storage using a URL? Examples would be most helpdul.

    Thanks,

    Mike

    Sunday, February 9, 2014 11:03 PM

Answers

All replies

  • I have the SQL Server backup to blob storage working when I manaualy run T-SQL commands. I would like to setup nightly backups going to blobl storage. This cannot be done throught maintenance plan UI in SQL Server 2012. Can I use T-SQL to schedule nightly backups that backup to azure blob storage using a URL? Examples would be most helpdul.

    Thanks,

    Mike

    • Merged by Kalman Toth Monday, February 10, 2014 12:53 AM duplicate
    Sunday, February 9, 2014 11:02 PM
  • Hello,

    Yes, you can. Once you have created the BLOB storage, you need to create a credential.

    IF NOT EXISTS

    (SELECT * FROM sys.credentials

    WHERE credential_identity = 'mycredential')

    CREATE CREDENTIAL <credential name> WITH IDENTITY = 'mystorageaccount'

    ,SECRET = '<storage access key> ;

    Then schedule a backup command like this:

    BACKUP DATABASE <database> TO URL = 'http://blobstorage.blob.core.windows.net/usercontainer/file.bak' WITH CREDENTIAL = 'mycredentialname' ,COMPRESSION ,STATS = 5


    Hope this helps.
    Regards,
    Alberto Morillo
    SQLCoffee.com


    • Edited by Alberto MorilloMVP Monday, February 10, 2014 11:55 AM
    • Proposed as answer by Fanny Liu Tuesday, February 11, 2014 8:13 AM
    • Unproposed as answer by mbuckingham Tuesday, February 11, 2014 11:50 AM
    Monday, February 10, 2014 11:55 AM
  • Alberto

    You are correct that you need to do that get backup to blob storage working but I have backup to blob storage working.

    What I need is a way to schedule regular nightly backups to my blob storage. In other words backing up to blob storage manually is not a problem. Including blob storage backup as part of my maintenance plan is the problem.

    Thanks,

    Mike

    Tuesday, February 11, 2014 11:53 AM
  • Hello,

    You just need to create a backup job for the backup statement I provided above.

    http://blogs.msdn.com/b/sqlagent/archive/2010/10/12/create-a-database-backup-job-using-sql-server-management-studio.aspx

    Additionally you can add it to any maintenance plan by including the backup statement on a Execute T-SQL Statement Task.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by mbuckingham Wednesday, February 12, 2014 11:59 PM
    Wednesday, February 12, 2014 10:56 AM