Backup plan help required
-
Good afternoon,
Please consider the following:
I have 18 servers at two different sites. The number of DBs on each server varies but in column three I have included the total size of doing a full backup of all DBs.
Backups are written to a NAS (not local disk) and since we only have 2 NAS boxes, we can only perform backups n 2 servers at any one time. Having more than one server write backup files to the same NAS had caused issues in the past with write timeouts which caused he backup jobs to fail!
The Servers are mostly 2005 so no native SQL compression/encryption I'm afraid!
I have already completed the exercise of working out the appropriate recovery model of each database on all servers. The good news is there are no T-log backups. All DBs are set to SIMPLE with fortnightly FULLS backups + daily DIFFs.
The backups can only start after working hours so we have from 6PM to 9AM and all day Saturday/Sunday.
The problem is, no matter how I look at it, I can't see how I can move the backup files from Site A to Site B after the backups complete. There isn't enough time to compress/encrypt the backups then transfer them across the internet to the other site.
The only solution I have come up with so far is to purchase RedGate's SQL BAckup 7 (which I've tested and it's great) but then I will need 18 licenses which costs thousands of £££s!
I also thought about creating a process to perform intelligent backups (i.e. only backups databases that have changed etc.. ) but this is just going to create problems I just know it. I mean what happens on a day when all databases have changes and this is possible.
I'm not looking for someone to solve this problem for me but maybe someone will see something in this that I can't!
Thanks for your time.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
I may have missed something, but do all the Full backups have to be on the same day?
You mention fortnightly Full backups. You can pile up a couple of smaller servers on Day 1 (perhaps servers 1, 2, 4, 11, 17), for Full backups, and Diff backups for the rest of the servers. Then you have all day to compress and ship all those backups. Day 2, do Full on server 3, Diff on all the rest, during the maintenance window, then compress and ship during regular hours (that shouldn't interfere with anything database-wise). Day 3, Full on server 5, Diff on all the rest. And so on.
The Diff backups should be, per what you're saying here, significantly smaller and faster than the Full backups.
Would something like that work, or did you already try that and I just didn't read it correctly?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Really sorry for the late reply GSQUARED (why do I keep calling you CSGUARD?!)
I may have missed something, but do all the Full backups have to be on the same day?
No, we don’t have to do all full backups in one night.
Basically the setup is like this:
Site A
12 SQL Server Instances and 2 NAS devices.
Monday
Server 1 & 2 backup to NAS1 & NAS2
Tuesday
Server 3 & 4 backup to NAS1 & NAS2
… and so on.
The problem is, it takes too long to perform the backups so there isn’t enough time at night to use a compression utility like 7-zip to compress/encrypt the backups then move them off the network via secure ftp.
Even if I decide to do this during day time, and assuming the compression doesn’t take too much CPU cycles, surely transferring 100s of gigabytes of data across the network will affect our users?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
I don't know if this will help but I back up all my servers to a local disk on the same server each night then the next step in the sql agent job does an execute sql command task and Xcopy the backup files across the network to another location.
I also use Olla Hallengren's backup routine on many servers as it catches any newly created databases (Sharepoint is a pain for that) and I set the last step of that sql agent job is to xcopy all the files and subfolders from one server to another.
-
Abu Dina (10/24/2012)
The good news is there are no T-log backups. All DBs are set to SIMPLEAnd that's good news!!
So, none of your databases require a point in time restore?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (11/7/2012)
Abu Dina (10/24/2012)
The good news is there are no T-log backups. All DBs are set to SIMPLEAnd that's good news!!
So, none of your databases require a point in time restore?
I got all excited when I saw there's a new comment but this isn't helpful I'm afraid.
In my original post I said that I had completed the exercise of working out the recovery model for each database so yes, I did take into consideration the RPO and RTO.
I explained this to the business but I got no answers back so I told them that with this current setup we could lose up to 24 hours worth of data. They said fine.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply