Sql-server – Strategy for backup for databases with little use (almost archived databases)

backupsql-server-2012

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)

Thank you!

PS, i am not a DBA, but a developer.

Best Answer

This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.

As you said you are not using compression I believe you can get good relief when you use backup compression. Just forget about the CPU utilization I have used backup compression many a times and the CPU utilization is just 5-10 % more in extreme cases. I suggest you to use it but before that read The Documentation

I am looking for a back strategy that would not result in unnecessary backup size.

The backup strategy is to make sure you have valid backup when needed and using that you can loose as less data as possible, there is hardly a strategy for making backup size less unless you use data compression or backup compression.

Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?

Backup size as such is not influenced by much by recovery model it determines the amount of data loss you can have in case disaster strikes.

Moral: Use backup compression it might solve all your queries.

Related Question