Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup plan help required Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 9:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1376551
Posted Wednesday, October 24, 2012 9:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1376558
Posted Tuesday, November 6, 2012 9:45 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1381645
Posted Wednesday, November 7, 2012 5:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 559, Visits: 1,157
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.
Post #1381928
Posted Wednesday, November 7, 2012 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 6,162, Visits: 13,305
Abu Dina (10/24/2012)
The good news is there are no T-log backups. All DBs are set to SIMPLE

And 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"
Post #1381933
Posted Wednesday, November 7, 2012 6:40 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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 SIMPLE

And 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

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
Post #1381954
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse