Hourly differential backups with append option

Question

With simple recovery mode, a nightly full backup, and hourly differential backups, if the hourly differential backup is set to "append" will the differential file continue to grow day after day after day, or does the nightly full backup cause the very next differential backup file to be truncated so that the append starts anew with a "clean slate"?

Answer 1

If you are backing up with NOINIT the backup files will continue to grow if they have the same name and destination.

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options

These options operate on the media set as a whole.

{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Personally I always use INIT and a distinct file name (and/or backup the file that is overwritten before it is overwritten)

If you are taking hourly differential backups, you might want to consider changing to full recovery and doing hourly t-log backups. But I understand this is not always an option.

You don't have a good description of how you are protecting the full and differential backups, but you unless you have a good reason not to, you probably want each diff and full to use INIT and have date/time stamp in the file name.

If you must append your diffs for more than one day, you should probably validate you can restore. I have not tried it, but I suspect that restoring from appended diff is going to be painful at best.

Note that per this answer you can not use EXPIREDATE (backup expiration date) to keep rolling backups in the same file.