0

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

  • So much depends on your infrastructure and your setup. Are you using SSD for your data and backup drives? How fast is your storage? Are you using Storage on a SAN? Could you backup directly to tape? What is your backup strategy? Full Once a week, Differential every other day, hourly transaction log backup? How big are the databases? Are you using any third party tools or just native SQL Backup? Please provide more details. Thanks. – John aka hot2use Nov 8 '16 at 7:20
  • @hot2use I am using SSD's. The storage as all SATA, It's not a SAN. 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 – progrAmmar Nov 8 '16 at 7:28
3

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

  • The advantage of Ola's backup/maintenance solution is the ability to backup databases according to various filters. You can then have multiple backup jobs with different settings and group according to your preferences. Drawback of Ola's solution: procedures in the master or msdb database and a couple of tables. We replaced a third party tool with Ola's solution and were even able to backup a 2 TB database in 1 hour using the correct parameters and compression. – John aka hot2use Nov 8 '16 at 9:09
  • Thanks for the insight. I will try and convince the higher ups to your recommendation. Also I need to ask one more thing. Since I am working with Simple recovery model My backup strategy is Full backup every week and partial every day. After a disaster, when I am recovering the database, should I recover all partial backups or just the latest one after Restore? – progrAmmar Nov 8 '16 at 10:19
  • You'd recover the last full, plus the last corresponding diff. – Cody Konior Nov 8 '16 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.