how to automate DB restore to another server? The plan is to have 1 backup set that will contain full and transactional log backup. (Server1)

There is already a scheduled job that will append any transactional log to the file after 30 minutes. (Server1)

There is a shared network drive between Server1 and Server2. But the problem is automating it via T-SQL. Here is the script:

RESTORE LOG [db_test_rep] FROM  DISK = N'Z:\db_rep.bak' WITH  FILE = 19,  NORECOVERY,  NOUNLOAD,  STATS = 5


The issue with this is that everytime the scheduled job executes, it will update the backup file numbering + 1. So in this case on the next transaction log backup, when I script into action the restoration using SSMS, it will generate FILE = 20

Can you guys give me a simple workaround for this?
The best suitable solution in your scenario is LOG SHIPPING which is available in Web, Standard and Enterprise editions. I assume that your SQL server is not express as you mentioned There is already a scheduled job

If you want work with T-SQL, i don't think appending backup files is good idea, so you may want to change schedule job to create separate file for each backup. Then you can start with following query to get dynamic restore command based on backup history:

SELECT      [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            f.physical_device_name,
            ((b.compressed_backup_size / 1024) / 1024) as CompressedSize_MB, 
            CASE    WHEN Type = 'L'
                        Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
                    WHEN Type = 'D'
                        Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
            END as Script,
            b.is_single_user,
            b.user_name
FROM    [msdb].[dbo].[backupset] AS [b]
            LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
where   b.type in ('D', 'L') 
        and b.database_name = 'TestDAG' --- add your database name here
order by b.backup_finish_date desc