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.

Important

Database Backup You must first run a database backup before you can run the Log file backup.
When you first run the log file backup for the first time, it will create very large files, over 1 gb.
Perform the backup operation a few times until the Log files are small, around 2 MB or less.

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.

┌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]
│      ├─At 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]

Important

Updated: June 17, 2026. A single Quote was missing from the end of the mkdir string.

select @cmdFS = 'mkDir \\ServerName\SQLServer_Backup\Trans\' + FORMAT(getdate(),'yyyy') + '\' + convert(varchar, getdate(), 110) + '\'+FORMAT(GETDATE(),'hh-mm')+'&apos;

Copy

Search Site

Search Google

As you can see, there are two single quotes after the plus; there was only 1. It has now been updated.

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

[SQL Script for Trans Backup]

CFFCS | CarrzSynEdit: | SQL Script


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

Explanation of the script.

FORMAT(getdate(),'yyyy') = Year (2023)
This will create a year folder for your files. Since it is 2023, that will be the folder name.
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.
FORMAT(GETDATE(),'hh-mm') = hh-mm (12-00)
The trans logs should be backed up every hour; depending on your site's size, you might need to reduce this interval. Some sites have this for every minute. That is for sites like Facebook, YouTube, Google, etc...
Conclusion of Folder-structure.
\\ServerName\SQLServer_Backup\Trans\2023\02-10-2023\12-00

Step 3

┌[Scheduling]
├──From the left panel, click on [Schedules]
│     ├─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].
To test, click to expand [Jobs]
Right-click on the job name, and choose [Start job at step...]

For errors, you may get.

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