0

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.)

2
  • Alternative thought, do you actually need Partitioning?...in what ways are you currently using it?
    – J.D.
    Jul 12, 2023 at 21:32
  • I have made a different topic for my situation, so we do not mix arguments. This is the new topic: dba.stackexchange.com/questions/329238/…
    – Radioleao
    Jul 13, 2023 at 1:57

2 Answers 2

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.

0

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.

8
  • Sadly, split partition in different backup files is not possibile in simple recovery mode. And I got an error when I try to restore a database from full + differential backup, If I use only last log backup (the one after last differential backup). And I got an error when I try to restore a database from full + differential backup, and I try to avoid log restore.
    – Radioleao
    Jul 12, 2023 at 9:47
  • @Radioleao Not sure I understand what do you mean by "split partition". How often old data is needed for reads? Did you consider "archiving"(moving to another db) it? Jul 12, 2023 at 9:54
  • @Radioleao what do you mean by db partitions? Do you mean filegroups? Or all tables are partitioned with the same partition function and you somehow manage to split backup to all tables partitions? Jul 12, 2023 at 10:05
  • Old data are used almost everyday, by user and by night processes. I need data online for at least a couple of years. So, every MONTH_DATE I have a different FILEGROUP, and a partition scheme to arrange data across partitions. So: I read from all partitions, I read/write on the one partition of current month (I use bold/caps just to be clear, not to scream :-) ).
    – Radioleao
    Jul 12, 2023 at 10:10
  • Ok, tnx for clarification :-) Even if there are daily reads you still can move this data to another db and do unions or even create a view that does this, so this change wouldn't effect processes. Guess that what I would try to do in your case. Still little bit confused, do you do backups by partition function? Is this even possible? Sure you don't use filegroup backups? I know that you can save log backups to disk = NULL but this is not really a good practice... Jul 12, 2023 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.