locked
Transfer SQL backup file from database server to Azure daily RRS feed

  • Question

  • Hi,

    How to auto transfer SQL backup file from database server to Azure daily?

    Thanks and regards.

    Wednesday, January 4, 2017 3:22 AM

Answers

  • Hi yuong yee,

    We can create a Maintenance Plan to backup to Azure, please use the following steps:

    1. In Object Explorer, expend the folder Management, then right-click Maintenance Plan, select Maintenance Plan Wizard.
    2. In the Select Plan Properties, select an account for 'Run as:', as mentioned in the article given by Alberto, please grant the SQL Agent Service rights to the backup folder. Besides, click the 'Change…' in Schedule to change the time to back up, you can select Daily.
    3. After that click next and select Back up Database in the Select Maintenance Tasks step, click next.
    4. In the Define Back Up Database Task, select the database you want to back up and select backup to URL, in the Destination tag, enter the Azure URL.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Teige Gao Wednesday, January 4, 2017 6:04 AM
    • Marked as answer by yuong yee Thursday, January 5, 2017 1:48 AM
    Wednesday, January 4, 2017 6:04 AM
    • Proposed as answer by Ekrem Önsoy Wednesday, January 4, 2017 6:20 AM
    • Marked as answer by yuong yee Thursday, January 5, 2017 1:48 AM
    Wednesday, January 4, 2017 4:13 AM

All replies

    • Proposed as answer by Ekrem Önsoy Wednesday, January 4, 2017 6:20 AM
    • Marked as answer by yuong yee Thursday, January 5, 2017 1:48 AM
    Wednesday, January 4, 2017 4:13 AM
  • Hi yuong yee,

    We can create a Maintenance Plan to backup to Azure, please use the following steps:

    1. In Object Explorer, expend the folder Management, then right-click Maintenance Plan, select Maintenance Plan Wizard.
    2. In the Select Plan Properties, select an account for 'Run as:', as mentioned in the article given by Alberto, please grant the SQL Agent Service rights to the backup folder. Besides, click the 'Change…' in Schedule to change the time to back up, you can select Daily.
    3. After that click next and select Back up Database in the Select Maintenance Tasks step, click next.
    4. In the Define Back Up Database Task, select the database you want to back up and select backup to URL, in the Destination tag, enter the Azure URL.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Teige Gao Wednesday, January 4, 2017 6:04 AM
    • Marked as answer by yuong yee Thursday, January 5, 2017 1:48 AM
    Wednesday, January 4, 2017 6:04 AM
  • HI Teige, Alberto

    Is it recommended to take backups to azure using maintenance plan if backups size is more than 100 GB.

    If we have huge backups I believe it impacts the performance of the server.

    What would be best approach.

    We are planning to move our SQL daily backups to azure.


    Thanks, Dev SQL DBA

    Wednesday, January 4, 2017 8:57 AM
  • Hello Hanuman,

    I had a customer who used to backup their databases to an offsite location directly and it used to take about 9 hours every day for the backup process to complete. You can think of it as Azure, same concept.

    I suggested them to perform the backup operation against a local storage first and afterwards transferring the new compressed backup files to the remote location. After implementing this solution, backup operation took only 1,5 hours and after completion of the local backup operation the new backup files is transffered to the remote location. As long as it takes shorter than 24 hours to transfer the failes to the remote location, it's alright, because it doesn't affect the production environment and we have our backups at a local storage which makes us feel safer as we can reach to our backups right away and our backups are located at a remote location which makes us feel much safer in case of a disaster.

    If you are not compressing your backups already, do that. When I visit new customers, I still see most of them do not perform the backup operation using compression.

    I think 100GB is fairly alright to transfer to a remote location, like Azure. The time it will take to transfer the files depends on the transfer rate per second of your network and file size which will increase by time. So you will want to monitor this as well.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, January 4, 2017 10:00 AM
  • Thanks Ekrems,

    Yes, even we are planning to have backups locally and move to Azure once finished.

    We do use compression.

    Problem is we have around 1 TB of backups to move to Azure, however our retention plan is 2 days  so may not be a proper solution.


    Thanks, Dev SQL DBA

    Wednesday, January 4, 2017 10:18 AM
  • You have totally 1TB database backup files to transfer to Azure Storage daily and your retention period is only 2 days? Isn't that too low? What's the purpose if you don't mind?

    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, January 4, 2017 10:33 AM
  • Hi Ekrem,

    We do have backup of storage drive we take backups for now so it was not required for more retention on our data center.

    if required we can get backup from storage backup media.

    Hope this clears confusion. 


    Thanks, Dev SQL DBA

    Wednesday, January 4, 2017 10:44 AM
  • Hi Yuong,

    Do you know Backup to URL? Introduced in SQL 2014 and onwards. Below is the link.

    https://msdn.microsoft.com/en-in/library/dn435916(v=sql.120).aspx

    I have written a SP which executed daily and takes backup on Azure. Let me know If that is what you looking for.

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 4, 2017 12:54 PM
  • Hello Sunil,

    Correction: Backup to URL feature can be used since SQL Server 2012 SP1 + CU2, FYI.

    https://msdn.microsoft.com/en-us/library/jj919148(v=sql.110).aspx


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, January 4, 2017 1:02 PM
  • Hello Sunil,

    Correction: Backup to URL feature can be used since SQL Server 2012 SP1 + CU2, FYI.

    https://msdn.microsoft.com/en-us/library/jj919148(v=sql.110).aspx


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    cool. Thanks

    Kindly mark the reply as answer if they help

    Wednesday, January 4, 2017 1:05 PM