SQL Server – Optimizing Storage with .bak and .dif Files

backupsql server

I'm working with a number of different servers that are severely lacking in backups.

I want to implement a back up plan using simple mode with full backups weekly followed by a series of differential backups throughout the week.

Is it a viable plan to perform the full and differential backups by overwriting the same file? Again I am trying to limit the amount of storage used and from my understanding if I'm taking a differential backup on some interval the data is cumulative therefore only the most recent .dif file in conjunction with the full .bak file will be necessary. Therefore each time a differential backup is performed it would overwrite the previous. Is this safe or am I misunderstanding something crucial?

Best Answer

Each differential backup is composed of the pages that have changed since the last full backup. So if you take a full backup on Sunday, then diffs on Monday, Tuesday, etc. then restoring the database will only require Sunday's full backup, followed by the most recent day's differential.

However, by overwriting your diffs each day, you are losing the ability to restore to a previous point in time. Example: Let's assume you take backups at 1 AM daily. On Thursday afternoon, you discover that data got corrupted on Wednesday and you need to get back to the last known good state. You can't, because the differential backup at 1 AM Wednesday (which would be clean) has been overwritten by the Thursday 1 AM backup which is corrupt.

So to answer your question, it is safe if and only if you assume that you will never have to revert to anything but the most recent backup, and that backup is always correct and corruption-free. I am not staking my job on that assumption.

I would not endorse the scheme you're proposing. Storage is relatively cheap - how much is the data worth to the company? Move the backups off to another, larger, cheaper datastore on a regular basis if space is at a premium, so that you can comply with any data retention policies, regulations or laws as needed.