SYSTEM: I have a system with about 150 databases, each of about 2gb in size.
130 of those are mostly inactive but need to be online. Those are set to simple recovery, active dbs are set to full.
I have a daily full backup and hourly tran log backups. I am using sql server maintenance plan backup and I am backing up all user databases
PROBLEM This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.
QUESTIONS I am looking for a back strategy that would not result in unnecessary backup size.
I am not using differential backup (which I think would reduce the size of the backup of the inactive DBs) because I am afraid of having to deal with large set of files in case of having to rebuild from backup.
I know I could maintain different backups maintenance plans, and update it as a db goes inactive, and as I add new active databases, however, this is manual process and we add a few databases a month. I've done this before and mistakes were made...
Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?
How about the backup compression? I have never used it, not sure what this will do to my CPU usage (currently the DB server is typically <15% CPU)
PS, i am not a DBA, but a developer.