How to schedule a backup of SQL Server Transaction Logs.
In this two-part lesson, we will set up our SQL Server to do a Scheduled backup of our Transaction Logs, or Trans Logs for short.

Please follow the information for setting up SQL Server for backups and scheduling.
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.«
After you've completed the steps from the above link, perform the following.

1. Step 3
   After you have started the [SQL Server Agent].
   Click to expand.
   Right-click on [jobs] and choose [New Job]. When the window opens.
   Give it a [Name] = SQL Server Trans Backup
   Give a [Description] = My SQL Server Trans Backup
   Click on [Steps] from the [Left-panel]
   On the bottom of the page, click [New].
   When the Step window opens, give it a [Name] = Start Trans Backup
   From Type: choose [Transact-SQL script (T-SQL)]
   Then choose [Paste]
   

In the script below, change the [ServerName] to the name of the Network Server you want to backup, too

-- WORKING CODE --Script 2: Backup all non-system databases declare @cmdFS varchar(100) select @cmdFS = 'mkDir \\ServerName\SQLServer_Backup\Trans\' + FORMAT(getdate(),'yyyy') + '\' + convert(varchar, getdate(), 110) + '\'+FORMAT(GETDATE(),'hh-mm')+' EXEC master.dbo.xp_cmdshell @cmdFS --1. Variable declaration DECLARE @FSpath VARCHAR(500) DECLARE @name VARCHAR(500) DECLARE @filename VARCHAR(256) -- 2. Setting the backup path SET @FSpath = '\\ServerName\SQLServer_Backup\Trans\' + FORMAT(getdate(),'yyyy') + '\'+convert(varchar, getdate(), 110)+ '\'+FORMAT(GETDATE(),'hh-mm')+'\' -- 4. Defining cursor operations ---------------------------------1--------------------------------- DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases are excluded --5. Initializing cursor operations OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN -- 6. Defining the filename format -- SET @fileName = @path + @name + '_' + @month +'-'+ @day +'-'+ @year + '.BAK' SET @fileName = @FSpath + @name + '.TRN' BACKUP LOG @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

Explaintion of the script.

1. FORMAT(getdate(),'yyyy') = Year (2023)
This will place a year folder for your files to be placed in. Since it is 2023, that will be the name of the folder.
2. convert(varchar, getdate(), 110) = Month-Day-Year (02-10-2023)
Within the Year (2023) folder, the script will create a folder for each day a backup is created. In this case, on 02-10-2023.
3. FORMAT(GETDATE(),'hh-mm') = hh-mm (12-00)
The trans logs are best backed up every hour, and depending on your site's size, you might have to change this to something smaller. Some sites have this for every minute. That is for sites like Facebook, YouTube, Google, etc...
4. Conclusion of Folder-structure.
\\ServerName\SQLServer_Backup\Trans\2023\02-10-2023\12-00

Step 3

1. Scheduling
From the Left-panel click on [Schdules]
Choose [New]
Give it a [Name]: Trans Backup
[Schdule Type]: Recurring [x] [Enabled]
[Frequency]
[Occurs]: Daily
[Recurs every]: 1 day(s)
Daily frequency
[Occurs every]: 30 minute(s) (Change this to suit your server requirements)
Duration
[Start date]: 2/10/2023 - [x] [No end date]:
Read the [Description] to ensure this is set up for your server requirements.
Click [OK].

2. To test, click to expand [Jobs]
   Right-click on the job name, and choose [Start job at step...]

For errors, you may get.

1. Right-click on the new Job.
   choose [View History]
   Click on the top item to expand.
   Under Message: