Csdrhrt

I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.

Is there any problem doing this?

I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log
.

Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.

If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).

If you had the database in simple recovery model, and each database was only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.

Essentially, the decision comes down to answering these questions:

1. What is my recovery point objective?


2. What is my recovery time objective?

See Wikipedia for great details about what those two things actually mean.

If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general (link to my blog), to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.

In your case, I would also do a log backup after every diff backup just to ensure the log doesn't grow out of control.

I completely agree with Max Venom and like to add few things here - your last statement i.e.

Then I can restore only the full + diff , instead of full + log + log
+ log + log + log + log + log + diff.

is not correct. Differential backup stored all changes from last backup. So, lets take below example with Backup Schedule:

1. Full Backup - 12:30AM




2. Differential Backup - 8AM, 12PM, 4PM and 8PM




3. Log Backup - Every Half an hour.

In case your database crashes due to some unforeseen reason at 11AM, you need to follow below sequence of restoration for latest recovery time:

1. Restore Full Backup With No Recovery


2. Restore Differential Backup taken at 8AM with no recovery


3. Restore Log backup taken at 8:30AM, 9AM, 9:30AM, 10AM with no recovery


4. Restore Log backup taken at 10:30AM with recovery.

Now, lets take another example wherein your database crashes at 5:10PM, you need to follow below sequence of restoration:

1. Restore Full Backup With No Recovery


2. Restore Differential Backup taken at 4PM with no recovery


3. Restore Log backup taken at 4:30PM with no recovery


4. Restore Log backup taken at 5PM with recovery.

You don't need to restore previous differential backup as differential backups are incremental and you need to take latest differential backup only.

One more thing to add here - When you are restoring differential backup then, previously taken log backup doesn't make any sense for restoration. Size of differential backup always increases or remains of same size(if no operation done from last backup) from last full backup to next full backup.

All the above backups are linked with LSN(Log Sequence Number), you can read more about this at below links:

https://sqlbak.com/academy/log-sequence-number/

https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

https://www.sqlshack.com/understanding-sql-server-backup-types/

I hope above helps.

Log backups some times act as an escape hatch. Suppose your diff backup is 100 GB and your log backup is only 1 GB and you are having space issues and the diff failed. Now if a disaster happens at least you can recover from your log backups. This not applicable for your scenario but, this does happen. - Biju jose

Also bear in mind, with differential backups you can't restore to a point between them. Let's say you create full backup at 8 AM, differential at 12 PM and t-log at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between.

If you have a full backup 8 AM, t-log every hour, and differential at 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your t-log chain at least until 12 PM). - dbamex