Previous Code Entry
Left Arrow
Schedule to Backup SQL Server Transaction Logs
Current Code Entry
Next Code Entry
Windows 2016 Server How to reset the maximum rearm count.
Right Arrow
Backup SQL Server Transaction Logs
Article Entry Date: February 10,2023 @ 02:43:54 / Last Updated On: February 10,2023 @ 12:43:23
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



Explaintion of the script.
  1. FORMAT(getdate(),'yyyy') = Year (2023)
  2. 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.
  3. convert(varchar, getdate(), 110) = Month-Day-Year (02-10-2023)
  4. 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.
  5. FORMAT(GETDATE(),'hh-mm') = hh-mm (12-00)
  6. 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...
  7. Conclusion of Folder-structure.
  8. \\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]
    [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].
  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 Transaction Logs«
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.«