How to create initial full backup followed by transaction logs daily?

The size of full back up BAK file is 4.37 Gib. Is it too big for restoring daily?

Not really for the restore, but possibly for the transfer.

How do I go about doing the of only inserting new rows, update changed row and delete rows that have been deleted in the process? Any T-SQL samples possibly?

While this is a routine exercise in so far there are a lot places where people are doing this, I would advice you avoid as long as you can, since it is a quite an increase in complexity from what you have now.

And in the end, maybe there is an overall solution for this data sync between you and your customer. What, I don't know, since don't know any details of this process.

@Erland Sommarskog Thank you again.

To be clear, inserting new rows, updating changed rows and deleting rows that have been deleted are complex process? Just want to confirm before thinking about other solution and talk to my manager about it.

I could possibly have a client access to database (TestDB), but I need to make sure we provide the speed & horse power for them to query the data.

Currently, this database (TestDB) is sitting in VM SQL (with other production databases), so I need to somehow move to more segregated environment (Azure SQL we were using for external clients).

We had an issue before that other client tried to query the data in Azure SQL Server, and speed was too slow and later they asked for BAK file instead. But BAK size was much smaller on this client.

Thanks.

To be clear, inserting new rows, updating changed rows and deleting rows that have been deleted are complex process? Just want to confirm before thinking about other solution and talk to my manager about it.

It is not super-advanced, but it's a bit of development effort. Exactly how much depends on how well the tables are geared for it. And how many tables there are, since you need to code this per tables.

There a few more solutions to the problem like Transactional Replication, but all come with some level of complexity.

Maybe it is something you can't escape in the long run. But if you are copying this 5GB database within Azure, I'm inclined to think that this is the best solution for the time being.

@Erland Sommarskog Thank you for your feedback. I have total 50 tables, so it is not too bad. Maybe I should give a try at least.

Do you possibly have some T-SQL examples with bottom two tables?

truncate table [TestDB].[dbo].[as_assessment]  
insert into [TestDB].[dbo].[as_assessment]      
select * from  [SourceDB].[dbo].[as_assessment]

A generic way is using the Merge statement:

MERGE target t
USING source s ON t.keycol1 = s.keycol1
             AND t.keycol2 = s.keycol2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (keycol2, keycol2, ..., col11, col12, ....)
       VALUES(s.keycol2, s.keycol2, ..., s.col11, s.col12, ....)
WHEN MATCHED AND EXISTS (SELECT t.col11,  t.col12, ...
                         EXCEPT
                         SELECT s.col11, s.col12, ....) THEN
    UPDATE SET col11 = s.col11,
               col12 = s.col12, 
               ...
WHEN NOT MATCHED BY SOURCE THEN
    DELETE 
;

This is not very efficient for larger tables, so people try to make shortcuts by having a last_updated column or similar. But given your data sizes, the above pattern may work for you.

The purpose of that AND EXISTS is to detect if there any actual differences in rows that exist both in target and in source.

@Erland Sommarskog Thanks again.

To clarify (or go one more step), are bottom query somewhat look ok?

Also, what do we put after DELETE (last line)?

Merge TargetDB t
USING SourceDB s 
	ON     t.keycol1 = s.keycol1
   	AND    t.keycol2 = s.keycol2
WHEN NOT MATCHED IN TargetDB THEN
       INSERT (keycol2, keycol2, ..., col11, col12, ....)
       VALUES (s.keycol2, s.keycol2, ..., s.col11, s.col12, ....)
MERGE TargetDB t
WHEN MATCHED AND EXISTS 
	    (SELECT t.col11,  t.col12, ...
                 EXCEPT
             SELECT s.col11, s.col12, ....) 
THEN
   UPDATE SET  col11 = s.col11,
               col12 = s.col12, 
               ...
   WHEN NOT MATCHED IN SourceDB THEN
          DELETE 
;

Sorry. Also, regards to your previous comment "I'm inclined to think that this is the best solution for the time being". What is your suggestion for the best solution? Using Azure SQL or using BAK file?

To clarify (or go one more step), are bottom query somewhat look ok?

That is a pattern, you can from, yes.

Also, what do we put after DELETE (last line)?

A semicolon, which is the very last line. Terminating statements in T-SQL with a semicolon is optional for historical reasons, but for the MERGE statement, it is compulsory.

What is your suggestion for the best solution? Using Azure SQL or using BAK file?

Whatever you are doing now. So a .BAK file for the customers on a VM, for the customers using Azure SQL you use a BACPAC or whatever you are doing now.

@Erland Sommarskog Thank you again. Regards to "after DELETE", I was asking whether I need to specify any columns or table(s) in details, but I guess there is nothing, correct?

Correct. You may note that INSERT and UPDATE are not followed by a table name, as they usually are. In a MERGE statement this is given by the table name after MERGE.

Although, to be precise, you can add a WHEN clause after DELETE, just like I have for UPDATE in the example, if you only wanted to delete rows under certain conditions. But I can't see that this applies here.

The MERGE statement is quite complex, and I would recommend that you study it more closely before you start using it all over town.

@Erland Sommarskog Thank you again. I am having some difficulty with statement as I am getting a lot of errors (table has 95 columns originally) after I tried with the T-SQL code.

Bottom is, however, short version (only with three columns), but it did not have any red signs on syntax, but still got some error after I ran. Is there any areas that need to be corrected?

(DELETED IMAGE FOR CONFIDENTIALITY)

You mentioned that there is other option (using last_updated column or similar).

Can you explain to me what I should do if we use that approach?

There were some errors in my outline - IN should be BY. I have corrected this. Then you have added a few yourself. TARGET and SOURCE are keywords and should not be replaced.

I recommend that you study the Microsoft Learn page for the MERGE statement. I am sure that there are also good blog posts out there, but I don't have a link handy for you.

Forums are great for getting an outline to a solution, but it can get very inefficient if you need to ask everytime you run into problems.

You have a extra MERGE right in the middle of it. Remove that line, and the syntax is OK.

You also mentioned that there is other option (using last_updated column or similar).

I don't how your tables look like. I mean, if you don't have such a column you would need to add one. And make sure that it set everywhere you need. A major piece of work.

You can work around by adding a rowversion column, which SQL Server updates automatically for you. But you still need to figure out how to track deletes.

I don't want to denigrate you, but this kind of illustrates why I recommend you to stay with the full backup, because you should have a solution that is within your skills.

@Erland Sommarskog Finally, with corrections, the query worked.

Now, I need to come up with a plan to generate transaction log.Since, this thread is getting too long, I will close this and open a new thread for that matter. Thank you so much for all your help for last three days or so.

@Erland Sommarskog Quick question. In order to create TRN file, is using MERGE statements, normal process? I am currently getting hang of MERGE statements without issue, but I am not sure if this approach is a best option. Is MERGE statements ok with a size of TRN file daily?

That very much depends on many changes there are in the source. Given the small database size, my assumption was that the changes are moderate. In that case, a transaction-log file per database should not be an issue.

In any case, if any of your MERGE statements appear to hang, that has little to do with the transaction-log file. That could be due to incorrect join conditions in the USING clause. It may also be that your tables do not have indexes to support the MERGE. (They were supposed to be key columns, so there should be indexes to support the primary key. If you are following best practice that is.)

@Erland Sommarskog Thank you so much. I am just wondering how other company generates TRN file? Do they also have to create MERGE statements? I think MERGE statements would work possibly now (but I have not tested the whole tables yet for the size daily), but I am trying to understand what other options are there beside using MERGE statement to generate TRN file.

Other companies? There may not being very many other companies that are using transaction logs the way you do!

The normal use for transaction-log backups is for disaster recovery. Your server dies and mangles the disk, or someone makes a fatal UPDATE without a WHERE clause. The procedure is to restore the most recent clean backup (which is not always the same as the most recent!) backup and apply transaction logs.

Some sites also do log-shipping which is kind of what you are looking into, but they are using the logs for both log-shipping and disaster recovery. You are using it only for log shipping, since there is not need for disaster recovery for this database that sits between your production database and the customer's copy.

The sync problem is by no means unique, and there are plenty of solutions: Transactional Replication, Change Data Capture, Change Event Streaming and the list comes up. New solutions seem to pop up all the time. One reason for this abundance is that there so many different requirements and use cases.

For the little I know of your system, using MERGE appeared a simple low-tech solution for your moderate data volumes. Many of the solutions I referred to above are fairly complex to set up and maintain.

@Erland Sommarskog Sorry to bother you again. I tested running TRN file, and the size of TRN file is much bigger than size of BAK file. I only tested for 35 tables out of 50 tables so far, but BAK file size was almost 5 Gib, but TRN size for 35 tables was around 10 Gib. I still have 15 more tables to go now.

Inside MERGE statement, I connected all rows of both source and destination tables. I guess there is no way around it (beside connecting all rows), correct?

Also, on the generating TRN file, I did not include INIT.

Any advice? Thanks!

And the other 15 tables, you are not touching at all? Or are you still deleting all and re-inserting for those tables?

@Erland Sommarskog Thanks for your help again! For other 15 tables, it is same MERGE statements. I just wanted to break down to run the MERGE queries for prior 35 tables while I am manually creating statements for 15 tables (to save time). So, all process is same like below (with more columns on all others):

Merge [ThirdDB].[dbo].[lab_report_detail_note]  t       
USING [SecondDB].[dbo].[lab_report_detail_note]  s 
	ON	
	t.[NoteID] = s.[NoteID]
	and t.[LabReportDetailID] = s.[LabReportDetailID]
	and t.[Note]  = s.[Note]
 
WHEN NOT MATCHED BY TARGET THEN
  INSERT (
       [NoteID]
      ,[LabReportDetailID]
      ,[Note]  	 	   
	  )
       VALUES (	
       s.[NoteID]
      ,s.[LabReportDetailID]
      ,s.[Note]  	  	 	   	   
      )
WHEN MATCHED AND EXISTS 
        (   SELECT		
      t.[NoteID]
      ,t.[LabReportDetailID]
      ,t.[Note]	  	   	       		
							
 EXCEPT
      SELECT 
       s.[NoteID]
      ,s.[LabReportDetailID]
      ,s.[Note]     	     	   	  	   	   	       			
	 ) 
THEN
   UPDATE SET  
       [NoteID] = s.[NoteID]
      ,[LabReportDetailID] = s.[LabReportDetailID]
      ,[Note] = s.[Note]
   WHEN NOT MATCHED BY SOURCE THEN
   DELETE 
;

My questions were:

Inside MERGE statement, I connected all rows of both source and destination tables. I guess there is no way around it (beside connecting all rows), correct?

Also, on the generating TRN file, I did not include INIT.

Any advice? Thanks!

First of all, is their an index maintenance job running on the server? For the sake of testing make sure it is disabled.

Then initiate two databases with the current data from the source. Make sure that they are in full recovery mode and take an full backup of both, followed by an immediate transaction log backup of both.

Wait until the next day. Load one of the databases in the way you have done in the past, that is delete and re-insert. In the other database, run your MERGE script.

Take a transaction log backup of both databases, compare the sizes.

For this test, use the INIT option, so that you don't mistakenly appends to an existing file. (Again, INIT does not affect the size of the transaction-log backup, but if you append to an existing file, that will obviously affect the resulting file size.)

@Erland Sommarskog

Thank you so much for your help. I am having little difficulties understanding what you indicated, so let me just reiterate bottom to be clear.

Here is my process:

1. Moving data from Original DB to 2nd DB: sort data by facility ID (Truncate and Insert)
2. Moving data from 2nd DB to 3rd DB: using Merge statement to insert/update/delete only the change from previous day (but still this would require connecting all rows of each 50 tables). I basically connected all rows for source and destination tables.

So, the later two DBs are in FULL recovery mode (except the Original DB).

I tested to compare the size of TRN file of 2nd DB and 3rd DB. The size of TRN for the 2nd DB is smaller (like 9 GB (2nd) vs. 16 GB (3rd)) while the size of BAK file for the 2nd DB is only 4.6 GB.

BTW, where do we go disabling index maintenance and do I have to disable this only for testing? What is the reason for disabling?

Thank you.

The reason for disabling index maintenance is that index maintenance can produce a large amount of log records.

My idea is that you need to follow a path to test whether the MERGE operations actually helps to keep down the log backups in size so that shipping transaction logs actually makes sense. To this end, you should compare the size of log backups with the old and new methods.

Then again, if you arrive that you want the index maintenance running, then maybe it should be enabled when doing the tests. And if the log backup are bigger than the full backups, there is little point in pursuing this track.

In fact, I think you should discuss this matter with your manager, and see if it is not time to tell the customer that you don't think that this is a viable option.

I don't know how your savvy your customer is, but there is a fair chance that once you start to ship logs to them, they will apply them in the wrong way.

For a size of 5 GB, I don't think this is worth the hassle.

As for where to disable the index maintenance, I assume that there is some who has set up this server, that person should know.

And, oh, as long as you inesrting-truncating and sending full backup, there is zero reason for having full recovery for this database makes little sense.

@Erland Sommarskog Thanks for your tremendous help again!

I am planning with other engineer to test regards to index maintenance, but (1) this is only for a temporary testing, correct? (not a permanent fix).

When I tested the size of TRN file from the SecondDB (where it just filters the data from sourceDB & Truncate and Insert all rows), it came out to be around 16 GB.

When I tested the size of TRN file from the ThirdDB (where we apply MERGE with Insert/Update&Delete all rows), it came out to be around 16 GB as well.

I think there is no point of using the TRN file from the ThirdDB since both TRN files have almost similar size, correct/no?

I am thinking about separating the process into like 8 intervals to create 8 TRN files of 2 GB size now.

(2) Is this a good option?

(3) If I change the Recovery model from Full to Simple, would it make any difference for the size of BAK or TRN file?

Thanks.

I am planning with other engineer to test regards to index maintenance, but (1) this is only for a temporary testing, correct? (not a permanent fix).

If the index maintenance has a big impact on the log space, you will need to fix it permanently. It should be possible to disable it only for the transfer database.

I think there is no point of using the TRN file from the ThirdDB since both TRN files have almost similar size, correct/no?

The fact that they have the same size suggests one of:

1. Everything else is dwarfed by the index maintenance.
2. There is a flaw in your testing or your merge script.
3. There are actually so much data changes that the MERGE has the same effect as TRUNCATE + INSERT.

I am thinking about separating the process into like 8 intervals to create 8 TRN files of 2 GB size now.

I see no point in this. 8*2 = 16 and that's still more than the size of the full backup, isn't it?

(3) If I change the Recovery model from Full to Simple, would it make any difference for the size of BAK or TRN file?

You can't take transaction log backups in simple recovery.

But, hm, maybe BULK_LOGGED recovery is the way to go here? That could be worth testing with the TRUNCATE + INSERT model (not with MERGE). Important is that you use TRUNCATE TABLE to delete the rows, not DELETE. For the INSERT statements you should say:

INSERT tbl WITH (TABLOCK)

This gives you minimally logged statements, and this can hold down the size of the transaction log backups, Index maintenance may also be minimally logged.

@Erland Sommarskog Thanks for help again.

So, should I try changing the Recovery model from "Full" to "Bulk-logged" and run the "Truncate and Insert" statement and generate TRN?

Also, the original BAK file (full back up) is around 4.6 GB, so it is less than several TRN files (like 8 of 2 GB TRN files).

Try the bulk_logged recovery strategy. I am not sure that it will work out, but it is worth giving a try.

@Erland Sommarskog

• I tried INSERT tbl WITH (TABLOCK) at FULL Recovery mode.
• Then, at "FULL" Recovery mode, I did the FULL back up (BAK file).
• Then, I changed to "BULK-Logged" mode and ran the TRN file.

The size of TRN file came out to be 6 GB (which is much smaller than 16 GB).

Thanks so much!

My next question is, can I try INSERT tbl WITH (TABLOCK) at "BULK-Logged" mode?

Would it make any difference or work?

I am just not sure when I should generate BAK file (FULL back up).

You should have BULK_LOGGED recovery at all time. In fact, if you only had BULK_LOGGED recovery when you took the log backup, that has no impact. So if the log backup was smaller this time, that was due to other factors.

BULK_LOGGED is like FULL recovery, but some operations are minimally logged. This keeps down the transaction log in size (but not necessarily the log backups!). With BULK_LOGGED recovery you cannot do a point-in-time restore in all situations, but that is not an issue in your case.

@Erland Sommarskog

I tested with running TRN file at FULL-Recovery mode, and the size of TRN file came out to be around same - 6GB.

I think INSERT tbl WITH (TABLOCK) was the factor that made the huge success.

Thank you. I think with 6 GB, it should work. I will probably divide them into few pieces.

6 GB > 4.7 GB, so I am not sure that I see the point. But we'll see what your customer says.

@Erland Sommarskog I am trying to divide the occurrence of generating TRN file into something like 6 times, so each TRN file generates into 1 GB each. Thanks.

It's still 6 GB...

@Erland Sommarskog I agree. But, as long as client is ok with smaller pieces, I think, it should be ok (for now). We will see. Thank you!

@Erland Sommarskog

I have a question about using INIT or not on generating TRN files.

I am trying to create multiple TRN files between "Truncate and Delete" process.

I have total 50 tables, so I am thinking putting bottom script between every 10 tables, so I have total 5 TRN files.

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

1. This approach should be ok, correct?
2. Should I use INIT or not? I am guessing without INIT because size for each TRN file came out to be much bigger if I use INIT when I just test it, correct?

Thank you so much!

As long as you are writing to a new file every time, INIT will has no effect.

As I've said a few times, INIT only has an effect when you write to existing backup file. INIT => overwrite existing content. NOINIT => append.

@Erland Sommarskog So, since I will be writing to a new file each time (not to an existing file (with overwrite)), and the size is much smaller, I will take out INIT from the statement. Thanks.

@Erland Sommarskog What is backup compression? Our client is still not ok with 4.6 GB of BAK file, and I came across with this approach (by asking around) to generate smaller TRN files. I wanted to get a feedback from you (if I can). Thank you.

Backup compression is that you get when you use the COMPRESSION keyword in the WITH clause. Which you seem to already be doing.

@Erland Sommarskog Sorry to bother you. I was trying to find any solution with database itself (like disabling index), but it appears that I am really running out of an option using TRN file. What do you think about using Differential file instead of Transactional file? I am still trying to understand how Differential file work, but is that approach even feasible in my case (moving data completely from one database (using a filter))? Thank you!

You could do differentials, but if you delete all data in the tables and re-insert, the differential backup will be the same size more or less as the full backup. If you merge in only the deltas, it could be smaller. But somehow that did not workout with the log backup, and there is no reason that it would work any better with the diff backup.

Also, if you diff backups, you probably will have to ship a full backup every once in a while, since a diff backup has all the pages that have changed since the last full backup, so over time the diff backups will be bigger.

@Erland Sommarskog

For last 2-3 weeks, I have been still working with Azure Support ticket and meeting with break and fix team. It is still going on. A technical advisor in that team helped me to shrink the log fie in the database two days ago, so that currently, TRN file is about 6 GB instead of 11+ GB on the second database. The second database is where Truncate and Insert are happening with some criteria.

Basically, here is flow (to refresh your mind):

Source database --> Second database (Truncate & Insert with criteria) --> Third database (Merge statement), but I am giving up on the Third database because size of TRN is too big and too messy to maintain row changes.

Beside trying to figure out the way in the second database to get the minimum TRN file(s), now, I am trying to see if there is a way to capture the difference of daily change on the source database itself.

The source database basically gets data daily from 24 hourly log files and those log files just get restored into the source database (Standby / Read-Only) mode. The limitation I see is that there is no column like "LastModified" on any of tables. During the restoration of 24 hourly log files, they just fetched into database using bottom T-SQL statement. Sorry this is pretty long, but I wanted to share to give you a better picture.

-- 1 - Variable declaration 
DECLARE @dbName sysname
DECLARE @fileName sysname
DECLARE @standby sysname
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'us_multi_replica0' 
SET @fileName = 'LOG_us_multi_replica' 
SET @backupPath = 'F:\Yesterday\' 
SET @standby = 'F:\backup2022\us_multi_replica_Rollback_2024-09-21_21-45-08.bak'

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - check for log backups 
DECLARE backupFiles CURSOR FOR  
   SELECT backupFile  
   FROM @fileList 
   WHERE backupFile LIKE '%.bak'  
   AND backupFile LIKE @fileName + '%' 
OPEN backupFiles  
-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''  
	   + @backupPath + @backupFile + ''' WITH STANDBY = ''' + @standby + ''''
   FETCH NEXT FROM backupFiles INTO @backupFile  
   	 
EXEC (@cmd)
END 
CLOSE backupFiles  
DEALLOCATE backupFiles  

My question to you is, what can be done to capture the daily difference within the source database. Copilot mentioned about "designing snapshot metadata tables" or "compute deterministic row hash". Does it sound like a possible solution?

Thanks you so much and sorry to keep bothering you.