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)
- 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:
- Process data by night
- Full backup every sunday morning
- Differential backup on monday-saturday morning
- T-Log backup
When I restore data, the order should be:
- Restore of full backup
- Restore of all differential backup since the last full backup
- Restore of all T-Log backup since the last full backup
- Restore of Tail Backup
For example, on wednesday:
- Tail log backup
- Restore of full backup of day 1 (sunday)
- Restore of differential backup of day 2, 3, 4 (mon, tue, wed)
- Restore of t-log backup of day 2, 3, 4 (mon, tue, wed)
- 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.)