Backup process dilemma

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup process dilemma

Backup process dilemma

Rate Topic

Display Mode

Topic Options

Author

Message

Abu Dina

Posted Friday, October 12, 2012 9:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484, Visits: 2,122

Good afternoon,

I have 5 servers (1 SQL Server instance per server ALL SQL Server 2005).

See below for totals and time it takes to perform backups.

Server 1: 68 Databases - Full backup total file size = 200GB - Backup Time = 5.40 Hours
Server 2: 40 Databases - Full backup total file size = 156GB - Backup Time = 4.17 Hours
Server 3: XX Databases - Full backup total file size = 104GB - Backup Time = 2.51 Hours
Server 4: XX Databases - Full backup total file size = 92GB - Backup Time = 2.28 Hours
Server 5: XX Databases - Full backup total file size = 108GB - Backup Time = 2.55 Hours

Please note, the backup files are written to a NAS. I wonder, is it better to backup to local disk then move files to NAS or is this not going to make a difference?

I've been experimenting with RedGate Backup Pro and it's just great. According to my calculations, RedGate should be able to cut down the backup time from roughly 17 hours (for all 5 servers above) to just 3-4 hours. Even better, the compression ratio is just great so moving the files to a remote site will also take less time... really impressed with the tool!

However, I know this might sound strange but we have more than 15 production servers and this is just going to cost too much to get REdGate for each instance even with a discount.

Can anyone recommend ways to improve the speed of backups or should I just try to make a strong case for going with RedGate?!

Thanks.

-----------------------------------
http://www.SQL4n00bs.com

Post #1372232

opc.three

Posted Friday, October 12, 2012 9:56 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 6,695, Visits: 11,710

For SQL 2005 a third-party backup tool that does compression on the fly (like RedGate Hyperbac or SQL Backup Pro) will be a good investment. Upgrading is expensive inother ways, but considering the cost involved to outfit all your servers that you mentioned, it might make sense to present a cost-benefit analysis for upgrading to SQL Server 2012 where you have access to backup compression as part of the product.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1372396

george sibbald

Posted Saturday, October 13, 2012 10:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 5,264, Visits: 11,191

backing up to local disk should be quicker.

Experiment with striping the backups, you could see improvements, or if you run your database backups consecutively, run some in parallel to try and bring down the overall elapsed time.

---------------------------------------------------------------------

Post #1372435

Abu Dina

Posted Monday, October 15, 2012 2:22 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484, Visits: 2,122

opc.three

Good point. Going with a third party tool would cost £7k+ which is just too much.

george sibbald

Another good point but there is something else I didn't mention in my original message! I suppose I can have the backups run in parallel as they are on different machines but once the backups have completed, I need to transfer them across the network to a different site which is just going to take too long to do.

I seem to be going round in circles! lol

Thanks for your input.

-----------------------------------
http://www.SQL4n00bs.com

Post #1372591

« Prev Topic | Next Topic »

Permissions