Should I create multiple maintenance plans to backup more than 200 User databases

Question

I have a SQL Server 2008 with more than 200 User databases. Some are larger than others and every two weeks or so a new db gets created.

Should I create only 1 maintenance plan backing up all 200+ databases or should I break them into 3 separate maintenance plans.

How much load does a backup plan generate on the server. Will it affect performance if I have them in the same Plan.

Some more details regarding the infrastructure.

I am using SSD's. The storage is all SATA, It's not a SAN but I have a network drive where I store the backups. I cannot backup to a tape. My backup strategy is Full once a week Differential every day. I am using SImple backup (Due to storage restrictions, cannot let logs grow and cannot have logs backup). The databases as I mentioned are roughly 10 GB around 10 or 12 DBs are 100 GB+ and I cannot use third party tools. I can create Maintenance plans

Answer 1

I wouldn't use maintenance plans but either Hallengren's backup solution, or, Minion Backup (the latter would be better because you can still have a single job, but write a table into each database which tells it which day it backs up on). In your comment you said you can't use "third party tools". I wouldn't go so far as to call these that, they're scripts, and whatever red tape there is I'm sure you can work around it by asking.

Question: How long do all the backups take?

I would likely go with the single job assuming all backups can complete in a reasonable amount of time. But key to this is using operational validation - writing something to check that all databases have been backed up, and also testing the backups themselves on a cycle.

If you wanted to go the other route - staggering backups - I've also done this on lots of large (at the time) backups to slow storage. I wrote/used a simple formula to run across the databases and determine which day each backup would run.

• Pick a measure for the databases (cost in minutes).
• Construct a table of databases ordered from most cost to least cost.
• Construct a table of the backup days, with slots for database name and cost.
• Iterate the database table.
• Find the next slot in the table of backup days with the least cumulative cost.
• Move the database into that slot.
• Repeat until there are no databases left.
• You are left with a list backup days, databases, and costs that balance out surprisingly well.

So you'd then use that to set a different backup for each day with those databases (and something to catch anything left over - which is going to be pretty difficult).