Off-site backups

Question

Post reply

Off-site backups

PhilipC

Hall of Fame

Points: 3124
More actions
November 15, 2018 at 8:03 pm

#380873

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?

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

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

PhilipC Hall of Fame Points: 3124 More actions · Answer 2

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 Guru Points: 50117 More actions · Answer 3

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: 1004318 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

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

mark.humphreys SSC Eights! Points: 867 More actions · Answer 5

mark.humphreys

SSC Eights!

Points: 867

November 16, 2018 at 9:04 am

#2013804

Are these physical servers or VMs?
Thanks
Mark

PhilipC Hall of Fame Points: 3124 More actions · Answer 6

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 SSCoach Points: 16154 More actions · Answer 7

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: 3124 More actions · Answer 8

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: 1004318 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 10

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

mark.humphreys SSC Eights! Points: 867 More actions · Answer 11

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: 125560 More actions · Answer 12

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: 125560 More actions · Answer 13

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: 3124 More actions · Answer 14

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?