SQL Server Performance Forum – Threads Archive

Backup Jobs

I’m wondering the best way to manage my backup jobs. We have two servers for production databases, all have high uptime requirements. Full Recovery on all. Server 1:
45 databases, smallest is 5 MB, largest is 5.5 GB
Currently I have four jobs running. The first job is a nightly, full backup that backs up all databases. The second job is a differential backup of all databases every 4 hours. The third job is a transaction log backup that initializes the transaction backup set each morning at 6 AM. The fourth job is a transaction log backup that runs every 30 minutes. This server requires high uptime and has heavy usage from 1:30 AM to about 8 PM EST (to accomodate Europe and West coast). Server 2:
Very high availability requirements, backbone of the business. (These databases are separate, but all work together and move data back and forth, hence the reason they are on the same server.
1 database is 10 GB
1 database is 120 GB
1 database is 65 GB
Two smaller DBs, less than 2 GB
Will add another in next 6 months, close to 50 GB to start.
Currently I have four jobs running. The first job is a nightly, full backup that backs up all databases. The second job is a differential backup of all databases every 4 hours. The third job is a transaction log backup that initializes the transaction backup set each morning at 6 AM. The fourth job is a transaction log backup that runs every 10 minutes. All backups are to disk. A tape backup runs nightly to pick up the BAK files. Question: Should I group all backups for all databases in a single job, or separate them out individually. If I separate, should I stagger the times of the backups, or can they all run at once (say, 8 PM?) Or should I keep the plan as constituted. Any advice on tuning this backup plan for better efficiency/recovery would be appreciated. Thanks

The common practice by creating a maintanance plan daily running jobs
Job1)backup job for all system DBS which run in a specific time
Job2)backup job for all user DBS which run in a specific time
Job3)Logbackup for all DBs,having full recovery model (in an intervel of 2 or 3 Hrs) . weekly jobs
job1)Optimisation for system dbs
job2)Optimisation for user dbs
job3)integrity check for system dbs
job4)integrity check for user dbs.
If you are not much sure about how to create/schedule job better go for maintannance plans.

Mike, The thinking for this varies somewhat but speaking from experience I would say. 1) Avoid going for the maintenance plan for the full/differential backups. The maintenance plan can be a bit of a black box when it comes to diagnosing errors and it’s better to write the code yourself, which makes it easier when it comes to fixing things that go wrong. 2) Generally I would say it is better to have a seperate job for each backup. Alot of people prefer one job that takes care of all backups but the problem with this approach is a) if you have large databases then they are backed up sequentially whereas if they are in seperate jobs they can be backed up in parallel and b) if one of the backups fails there is a chance that the remaining backups will not even run, whereas in seperate jobs any one failure does not affect any other backup job. In situations where the databases are small point number 2 doesn’t make much difference but I would say it’s good practice anyway. The only time I have seen it worthwhile throwing in all backups under one job is when we were administering a SQL Server used for sharepoint (I think) that had something like 400 databases, and obviously, creating 400 backups job was a bit too much, not to mention that scheduling them would have been problematic. Regarding your particular environment I would say that you could probably do without the differential backups on the smaller databases (<10GB). The only thing this really gives you is that you’d need to restore fewer log backups if you ever needed to restore a database. That’s a matter of personal choice though. Regarding your transaction log backups, you could definately do away with the third job that you use to initialise the tran log backup set. What I would do instead is use the database maintenance plan (ignore what I said about them earlier on <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />). The good thing about the maintenance plan for tran log backups is that it backs up the log to a new file each time with its own timestamp. All you need to do then is specify how long you want to keep the tran log backup files. I would recommend that you keep the files for 48 hours at least. By only keeping 24 hours you could compromise yourself. If a full database backup fails overnight, when you come in in the morning your most recent backup would have been something like 30 hours ago, and with only 24 hours worth of log backups you’d be stuck. Of course, if you’re backing up to tape you’ll be ok but it’s easier to get it off disk. Also, if your tape backups fail you’re still covered by having 48 hours worth of log backups. Regarding the schedules, your databases are relatively small (except for the 120GB one) so I don’t really see a problem backing them all up at once. Because the backup disk will be under more performance stress with all backups happening at once it might take a little longer than normal but it won’t adversley affect the databases too much. Hope that helps Karl Grambow www.sqldbcontrol.com

]]>