How can I create TRN file to capture only difference daily (between TRN files)?

Question

How can I create TRN file to capture only difference daily (between TRN files)?

Justin Doh 980

I am trying to understand how Transaction Log works.

This question is related to my previous post.

My requirement is, instead of providing BAK file (because it is huge), I am trying to generate one TRN file daily for any change (insert, update or delete) from previous day's TRN file.

So, I run the bottom script to generate a BAK file. There is no issue on this.

BACKUP DATABASE TestDB  
TO DISK = 'F:\TestDB.bak'  
WITH COMPRESSION, INIT;

Then, I run the bottom script to run TRN file daily, correct?

BACKUP LOG TestDB  
TO DISK = 'F:\TestDB_Log.trn'  
WITH COMPRESSION;

What is difference between top and bottom script (with or without INIT)?

BACKUP LOG TestDB  
TO DISK = 'F:\TestDB_Log.trn'  
WITH COMPRESSION, INIT;

The reason I am asking this is when I ran the bottom code (with INIT) for testing, the size of TRN file was relatively big, so I am curious whether bottom one captures everything from original BAK or not. Should I delete INIT?

BACKUP LOG TestDB  
TO DISK = 'F:\TestDB_Log.trn'  
WITH COMPRESSION, INIT;

My goal is create a TRN file daily only for the difference between each TRN file (not accumulative differences from BAK file).

Please let me know. Thank you.

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Erland Sommarskog 133.2K MVP Volunteer Moderator

INIT means that you want to override the existing backup file. If you are not using INIT, you will append to the existing file, so that file will keep growing. Given your use case, you should use INIT. Having multiple backups in the same file, is bound to cause confusion.

Normally, you write each backup to a distinct filename, but in your use case that may not be necessary. That depends on how your client wants to have it on their side.

As for the size of the transaction-log backup, I can understand if the first backup was big if you had not backed up the transaction log for a while. In that case, the transaction-log backup includes all changes since that previous log backup. That is, there is no relation to your most recent full backup.

The transaction-log backups are entirely discoupled from the full backups with one exception: When you take your first transaction log backup, it will go back to the very first full backup after you put the database in FULL recovery mode.

For your, quite special scenario, you would do this:

As long as you are only sending full backups, set the database in simple recovery.
Once you have completed all your MERGE statements so that you can make incremental updates, switch to full recovery.
Take a full backup and send to customer.
From now on, you send daily TRN log backups.

Note that if the client is busy and not apply the backup they get on the 22nd, they cannot just ignore that and apply the file they get on the 23rd. They must apply all transaction logs. And they must restore with the WITH STANDBY option.

Justin Doh 980 Reputation points

2026-02-05T22:37:12.3133333+00:00
@Erland Sommarskog Thanks again.

Actually, I am using bottom script to stamp day and time.

If that is the case, should I still use INIT?

declare @backupfileLOG nvarchar(100) SET @backupfileLOG = 'F:\TestDB\TestDB_TR' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.TRN' BACKUP LOG preciseMDS_TR to disk = @backupfileLOG WITH COMPRESSION;

Also, what I am not clear is the whole process.

If I send a client BAK file (when status of db is Full back up), then send daily TRN file (with daily time stamp), that should be sufficient, no?
Erland Sommarskog 133.2K Reputation points MVP Volunteer Moderator

2026-02-05T22:45:16.3466667+00:00

In that case, it should not matter. And thinking more of it, it is better to leave it out, in case the job would run twice with the same file name. In that case, it will append to the file, and the client must apply both backups.

(Think of the log file as a script with all changes made to the database. It is not a script written in text, rather a binary script. But it is the same idea. You need all statements, and you cannot take something in the middle.)
Akhil Gajavelly 1,570 Reputation points Microsoft External Staff Moderator

2026-02-12T05:46:34.8966667+00:00

Hi @Justin Doh

Thank you @Erland Sommarskog for the clear explanation and especially the log backup analogy that really helps clarify the process.

@Justin Doh , if this answers your question and helps you move forward with your backup strategy, please consider marking it as the accepted solution so it can help others as well.

Thanks,
Akhil.

Answer 2

To create a TRN file that captures only the differences (i.e., the transaction log) from the previous day's TRN file, you can use the BACKUP LOG command as you have shown. Here’s a breakdown of your questions:

Difference Between Scripts with and without INIT:
- The INIT option in the BACKUP LOG command specifies that the backup file should be overwritten. If you use WITH INIT, it will replace the existing TRN file with the new backup. If you do not use INIT, the new TRN file will be appended to the existing TRN file. This means that without INIT, the TRN file will accumulate logs over time, which may lead to larger file sizes as it includes all transactions since the last full backup.
Size of TRN File:
- If you notice that the size of the TRN file is relatively large when using INIT, it may be because the log contains all transactions that occurred since the last full backup or the last log backup. If you want to keep only the transactions from the last TRN file, you should not use INIT and instead append to the existing TRN file.
Creating Daily TRN Files:
- To achieve your goal of creating a TRN file daily that captures only the changes since the last TRN file, you should run the BACKUP LOG command without the INIT option. This way, each TRN file will contain only the transactions that occurred since the last log backup, allowing you to manage the size of your TRN files effectively.

In summary, use the following command to create a TRN file daily without overwriting the previous one:

BACKUP LOG TestDB  
TO DISK = 'F:\TestDB_Log.trn'  
WITH COMPRESSION;

This will ensure that you are capturing only the transactions that have occurred since the last log backup without accumulating all previous transactions from the BAK file.

References:

Share via

How can I create TRN file to capture only difference daily (between TRN files)?

1 additional answer

Your answer