Previous Code Entry
Left Arrow
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.
Current Code Entry
Next Code Entry
Backup SQL Server Transaction Logs
Right Arrow
Schedule to Backup SQL Server Transaction Logs
Article Entry Date: February 9,2023 @ 11:57:48 / Last Updated On: February 10,2023 @ 12:43:30
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 2
    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 Transaction Logs Backup
    Give a [Description] = My SQL Server Trans Logs 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 Database Trans Logs 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

Explaintion of the script.
  1. The only string above that separates [Database backup] from [Transaction Backup] is the 4th line-up.
    [Transaction Log]
    BACKUP LOG @name TO DISK = @fileName
    BACKUP DATABASE @name TO DISK = @fileName
  2. FORMAT(getdate(),'yyyy') = Year (2023)
  3. 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.
  4. convert(varchar, getdate(), 110) = Month-Day-Year (02-10-2023)
  5. 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.
  6. FORMAT(GETDATE(),'hh-mm') = hh-mm (12-00)
  7. 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...
  8. Conclusion of Folder-structure.
  9. \\ServerName\SQLServer_Backup\Trans\2023\02-10-2023\12-00

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

Other Articles Related to this Entry.

Schedule to Backup SQL Server Databases«
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.«