Should I create multiple maintenance plans to backup more than 200 User databases 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
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).