| Author |
Topic |
|
10Dawg
Starting Member
46 Posts |
 Posted - 2012-09-06 : 12:16:11
|
| I backup nightly, 93 databases (1.05TB total) on one instance. I use the built-in sql backup plan (Maintenance Plan Wizzard) and do full backups. It takes almost all day to backup and over a day to 7-zip them and store them on my backup drive so I'm fightig a losing battle at this point. I need some ideas to make this less time consuming and more automated.Swith to distributed set up (Clustering)? Third party backup program? Any advise will be appreciated.10Dawg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2012-09-06 : 13:03:39
|
| sql server 2008 R2, sql 2008 enterprise10Dawg |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-09-06 : 13:25:53
|
| Are you using compressed backups? It is an option in the backup task - and by default is turned off. It should be available to you since you are running Enterprise Edition.That will speed things up a bit - and compress the backups for you instead of using 7-zip which will save you at least a day.However, taking a full day to backup 1.05TB is taking way too long as it is. This indicates that you have a problem with the IO subsystem and SAN configuration. If you are not on a SAN and you are backing up to local disks - then you definitely have serious issues with the drive configuration. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-06 : 13:52:44
|
| You should also consider going to SQL 2012, where you can do your backups from a secondary replica.-Chad |
|
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2012-09-06 : 13:53:01
|
| yes I'm backing up to a local disk and then moving them. Where do I read up on the recommended drive configuration?10Dawg |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-06 : 14:03:29
|
| And why are you doing nightly FUlls? Why not Weekly Fulls, and nightly Diffs?-Chad |
|
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2012-09-06 : 14:30:52
|
| Company policy. I've argued against it several times.10Dawg |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-09-07 : 11:37:59
|
| Company politics YUK! I feel your pain 10Dawg.You have several options as suggested above but perhaps a review of your RTO & RPO for the 93 might convince management to be more flexible with their backup plan? Are all these DBs production OLTPs? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-09-08 : 11:25:31
|
quote:
Originally posted by chadmat
And why are you doing nightly FUlls? Why not Weekly Fulls, and nightly Diffs?-Chad
If you have the maintenance window - why not daily fulls?I perform full backups of my 3TB database every night and complete the backup in less than 2 hours. That is well within our defined maintenance window. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-08 : 12:55:40
|
quote:
Originally posted by jeffw8713
quote:
Originally posted by chadmat
And why are you doing nightly FUlls? Why not Weekly Fulls, and nightly Diffs?-Chad
If you have the maintenance window - why not daily fulls?I perform full backups of my 3TB database every night and complete the backup in less than 2 hours. That is well within our defined maintenance window.
There is nothing wrong with it per se, but (Unless most all of your data is modified daily) you will save disk space and shorten your maint window by utilizing diffs daily.-Chad |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-09-09 : 13:00:47
|
quote:
Originally posted by chadmatThere is nothing wrong with it per se, but (Unless most all of your data is modified daily) you will save disk space and shorten your maint window by utilizing diffs daily.-Chad
That really depends on the system and the RPO/RTO requirements. You shouldn't look at performing differentials just to save space on disk. It has to be considered for all aspects of your recoverability.My backups take 2 hours because of the way we are configured. If I had the opportunity to change the configuration, add HBA's and create a private backup network I could easily reduce that down to 30 minutes or less. If my RPO/RTO requirements demanded the ability to recover in less than 2 hours - I would be able to do that, but the RTO for the application doesn't require it. |
|
|
|
|
Backup Plan takes too long