1

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.

  • 1
    Have you taken a look at ola hallengren's scripts? And always use your backup compression to reduce the size especially with your CPU load. ola.hallengren.com – Stijn Wynants Dec 20 '16 at 14:29
  • @StijnWynants yes, i am using ola's scripts for integrity check, but not for backup... a little afraid of screwing up with it :( – Greg Balajewicz Dec 20 '16 at 14:30
  • Just use the ola scripts for backup, almost every dba in the world uses them. And enable your backup compression to be default. Are you doing backups to a local disk or to a share? – Stijn Wynants Dec 20 '16 at 14:31
  • @StijnWynants i am backing up to a share (over a network). I've looked at this script, but I dont see a way to differentiate between the active and inactive DBs. Are you saying that with compression this is not necessary? – Greg Balajewicz Dec 20 '16 at 14:34
  • Question is do you want a full backup of the inactive databases? What is your RTO? Are the inactive databases read only or can data change? You can create 2 jobs one called inactive and one active. And then specify the database list for the inactive ones and exclude them from the other job ola.hallengren.com/sql-server-backup.html look at the parameters for database – Stijn Wynants Dec 20 '16 at 14:38
1

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.

0

Based on comments from everyone, I've decided to use ola hallengren's scripts and backup compression. I was not able to have two backup sets automatically, but I was able to easily exclude a few large databases that did not need a backup which also helped.

I did not notice any effect on CPU usage when backup up with compression.

Thanks to everyone for your suggestions

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.