Sql-server – Should I create multiple maintenance plans to backup more than 200 User databases

backupmaintenance-planssql serversql-server-2008-r2

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

Best Answer

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).

Related Question