MS SQL full backup by partition, can be done without log?

Question

The matter is that I really don't need t-tlog and i wish to avoid it. I'have looked in many topics but I haven't seek out the answer I need (the topic of MSSQL backup is very tricky to understand well in every aspects).

My database is made like this:

• 15 tables of configuration data
• 3 tables with huge amount of rows per month (overall are 30M-50M rows per month)
• Data of current month are written/updated, every night
• Data of past months are only stored (do no change anymore)
• Partitions:
  • 1 partition for primary
  • 1 partition for configuration data
  • n partitions for months data (1 partition for each month)

Every night, many processes recalculate all month data; every morning there are few changes from the day before. This means also: a huge transaction log compared to few final data chances).

I have this process/backup configuration:

1. Process data by night
2. Full backup every sunday morning
3. Differential backup on monday-saturday morning
4. T-Log backup

---

When I restore data, the order should be:

1. Restore of full backup
2. Restore of all differential backup since the last full backup
3. Restore of all T-Log backup since the last full backup
4. Restore of Tail Backup

For example, on wednesday:

1. Tail log backup
2. Restore of full backup of day 1 (sunday)
3. Restore of differential backup of day 2, 3, 4 (mon, tue, wed)
4. Restore of t-log backup of day 2, 3, 4 (mon, tue, wed)
5. Restore of tail log, WITH RECOVERY

My questions follows.

Question 1: Since I have restored all differental backups, why I need all log backup since last full? Why is not enought only t-Log since last diffential backup?

Question 2: Can I restore only full and differential backup, and bring my database online with no log restore?

Question 3: After night data processing, I'm sure that no other data are changed. This means that when I made differential backup in the morning, I don't need to store the huge t-Log data. Can I truncate transaction Log before Log Backup? Or can I completely avoid t-log backup?

Question 4: I don't really need full T-Log but I choose FULL recovery mode instead of SIMPLE mode because I want to split backup of different partitions into different files, for pratical reasons. ARE there any other configuration, or do you have suggestions, for that?

Question 5: Since data of past months never changes, I do not need to backup these every time I do a full backup. Is there any way to mix past backups (of every past month) and new backups (of primary, operational data and current month)?

Thanks a lot. (I know It's hard to be precise in this stuff. If any point is not well explained, tell me and I will try to do better.)

Answer 1

When I restore data, the order should be: Restore of full backup

Yes

Restore of all differential backup since the last full backup

No. Diff backups are cumulative so only last diff backup should be restored

Restore of all T-Log backup since the last full backup

No. Restore log backups only since differential backup.

Restore of Tail Backup

Only if you participate in log shipping. In other case you don't create tail log backups.

For example, on wednesday: Restore of full backup of day 1 (sunday) Restore of differential backup of 4 (wed) Restore of t-log backup after wed until desired recovery point

Question 1: Why is not enought only t-Log since last diffential backup?

Should be enough. Try to restore (you have to practice restore anyways) and provide an error message if it didn't work

Question 2: Can I restore only full and differential backup, and bring my database online with no log restore?

Yes. It will be in the state of when diff backup completed.

Question 3: can I completely avoid t-log backup?

I am not sure what problem you are trying to solve. Do you have huge log file? Basically in full recovery mode you must run log backups to prevent log file from growing.

Question 4: I don't really need full T-Log but I choose FULL recovery mode instead of SIMPLE mode because I want to split backup of different partitions into different files, for pratical reasons. ARE there any other configuration, or do you have suggestions, for that?

So you mean you don't need point in time restore but you use full recovery mode for partial backup/restore. Looks like you need Piecemeal Restores https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/piecemeal-restores-sql-server?view=sql-server-ver16 For simply recovery mode you should declare your old filegroup as read-only https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver16

In this case you can skip read-only filegroups from backup and restore

Question 5: Since data of past months never changes, I do not need to backup these every time I do a full backup. Is there any way to mix past backups (of every past month) and new backups (of primary, operational data and current month)?

Yes. you should use READ_WRITE_FILEGROUPS clause in your weekly backup statement https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#read_write_filegroups---filegroup---logical_filegroup_name--logical_filegroup_name_var---n--

However if you try to write to any object in read-only tablespace your call will fail.

Answer 2

Not a great backup expert but I'll try:

1. Log backups are needed to restore to last point in time. Each log backs tran log and truncates it. So last log file will only have data since last log backup. *Believe that you need all log backups since last diff backup, not last full.
2. Not sure, believe that you can, you should try it in lower env. You'll just lose all changes after diff backup
3. You can run shrink command but it won't release used space. In full recovery mode only way to reduce tran log used space is backup.
4. I believe this is the main question. Do you really need full recovery mode if you don't need or use tran log backups? If you don't care about losing data between full\diff backups(only if you absolutely sure), you should definitely reconsider using full backup. You can split backup files across different discs in simple mode also. You just won't save log backups that(according to what you've wrote) you don't need anyway.
5. Don't think that you can mix restores between different(old and new) backup files.

Having said all that you should recheck what is happening on your DBs. Log files don't just grow by themselves. If they do means something does happens. But again if I was completely sure that I don't need log backups I would avoid using full recovery mode.

I would also consider archiving process - moving all old data to other server\db and work with relatively small DB so backups won't be huge.