Off-site backups

  • PhilipC

    Hall of Fame

    Points: 3076

    I've recently started in a new organisation and found that 2 local SQL Servers Backups are not going off-site at all. One is a 2008 R2 Server and another is SQL 2014.

    I was planning to purpose direct backups to Azure but that isn't an option within 2008 R2. 

    What kind of options do I have here to achieve this in a cost effective way?

  • Jeff Moden

    SSC Guru

    Points: 994555

    2008 is now a decade old.  Now would be a good time to do an upgrade.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • PhilipC

    Hall of Fame

    Points: 3076

    Would love to, only recently started in the organisation and they have heaps of SQL 2000, SQL 2005 instances too.  Massive clean-up required but off-site backups are needed to be sorted out first as a priority before any upgrades. 

    But you didn't answer my question 😛

  • jasona.work

    SSC-Forever

    Points: 49870

    Seeing as getting on  newer version of SQL doesn't sound like an option (near-term) you're going to need to look into some other method.
    Possibly setting up a job that copies the backup files from the SQL200X systems up to Azure (if that's even possible) using Powershell or the like, or to an off-site data storage over VPN (slow, slow, slow.)

    Possibly, maybe, you might be able to do something crazy like log-ship the databases to an SQL201X system, then back *those* up to Azure.  Not sure I'd want to have to troubleshoot that lash-up though!

  • Jeff Moden

    SSC Guru

    Points: 994555

    PhilipC - Friday, November 16, 2018 5:49 AM

    Would love to, only recently started in the organisation and they have heaps of SQL 2000, SQL 2005 instances too.  Massive clean-up required but off-site backups are needed to be sorted out first as a priority before any upgrades. 

    But you didn't answer my question 😛

    I first had to make sure that upgrades weren't an option. 😀   And hat's off to you for embracing something very important to any and every company.

    And apologies... I can't help on the cloud stuff for this type of thing... I'm very happily "stuck" in the on-premise world.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • mark.humphreys

    SSC Eights!

    Points: 867

    Are these physical servers or VMs?
    Thanks
    Mark

  • PhilipC

    Hall of Fame

    Points: 3076

    jasona.work - Friday, November 16, 2018 6:11 AM

    Seeing as getting on  newer version of SQL doesn't sound like an option (near-term) you're going to need to look into some other method.
    Possibly setting up a job that copies the backup files from the SQL200X systems up to Azure (if that's even possible) using Powershell or the like, or to an off-site data storage over VPN (slow, slow, slow.)

    Possibly, maybe, you might be able to do something crazy like log-ship the databases to an SQL201X system, then back *those* up to Azure.  Not sure I'd want to have to troubleshoot that lash-up though!

    Yes it's a not a near thing as there's a lot of very old unsupported products that are being used by the business. It's a big job to consolidate and upgrade everything. 

    I was thinking Powershell too, but thought I'd open up the question to the community to see about ideas. Appreciate your comment Jason.

    Jeff Moden - Friday, November 16, 2018 8:51 AM

    PhilipC - Friday, November 16, 2018 5:49 AM

    Would love to, only recently started in the organisation and they have heaps of SQL 2000, SQL 2005 instances too.  Massive clean-up required but off-site backups are needed to be sorted out first as a priority before any upgrades. 

    But you didn't answer my question 😛

    I first had to make sure that upgrades weren't an option. 😀   And hat's off to you for embracing something very important to any and every company.

    And apologies... I can't help on the cloud stuff for this type of thing... I'm very happily "stuck" in the on-premise world.

    Yes it was a real immediate red flag, but they weren't even running CheckDB or Backups on any of their systems until I arrived a month ago. Databases with tens of thousands of customer based records that if they lost, they'd be absolutely screwed. It was a real shock in this day and age. 

    No problem, thanks for your input anyway Jeff.

    mark.humphreys - Friday, November 16, 2018 9:04 AM

    Are these physical servers or VMs?
    Thanks
    Mark

    VMs.

  • frederico_fonseca

    SSChampion

    Points: 14086

    powershell definitely an option - and possibly plenty of examples on net to do it.

    this may also be an option https://blogs.msdn.microsoft.com/markm/2015/06/30/easily-backup-sql-databases-to-azure/

  • PhilipC

    Hall of Fame

    Points: 3076

    frederico_fonseca - Friday, November 16, 2018 6:14 PM

    powershell definitely an option - and possibly plenty of examples on net to do it.

    this may also be an option https://blogs.msdn.microsoft.com/markm/2015/06/30/easily-backup-sql-databases-to-azure/

    Thanks very much for that! I couldn't dig that up myself in a google search but seems ideal 🙂

  • Jeff Moden

    SSC Guru

    Points: 994555

    frederico_fonseca - Friday, November 16, 2018 6:14 PM

    powershell definitely an option - and possibly plenty of examples on net to do it.

    this may also be an option https://blogs.msdn.microsoft.com/markm/2015/06/30/easily-backup-sql-databases-to-azure/

    Awesome!  Thanks for the link, Frederico.  You gave me one heck of an idea that happens to fit right into the plan that someone wants to pull off for work.  I have no love for the cloud but this one could make a good dent in that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • frederico_fonseca

    SSChampion

    Points: 14086

    good to see possibilities 😀 and I didn't knew about it either!!!

  • mark.humphreys

    SSC Eights!

    Points: 867

    If you have VM's.  This software is very good.  Whilst it takes an image of the server it still allows for object level recovery.

    Fairly cost effective as well.

    https://www.altaro.com/vm-backup/

  • Eric M Russell

    SSC Guru

    Points: 125010

    First, manually copy the latest full backup for each database to Azure storage.
    2nd, setup automated job to copy backups to Azure.
    3rd, if you feel it's needed, research tools to backup directly from on-prem to Azure.

    You don't want this to fail under your watch.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell

    SSC Guru

    Points: 125010

    https://www.mssqltips.com/sqlservertip/4596/copying-sql-server-backup-files-to-azure-blob-storage-with-azcopy/

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • PhilipC

    Hall of Fame

    Points: 3076

    frederico_fonseca - Friday, November 16, 2018 6:14 PM

    powershell definitely an option - and possibly plenty of examples on net to do it.

    this may also be an option https://blogs.msdn.microsoft.com/markm/2015/06/30/easily-backup-sql-databases-to-azure/

    This link is dead now. Did anyone take a copy?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply