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.
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.
- The only string above that separates [Database backup] from [Transaction Backup] is the 4th line-up.
BACKUP LOG @name TO DISK = @fileName
BACKUP DATABASE @name TO DISK = @fileName
- 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.
- 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 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...
- Conclusion of Folder-structure.
From the Left-panel click on [Schdules]
Give it a [Name]: Trans Backup
[Schdule Type]: Recurring [x] [Enabled]
[Recurs every]: 1 day(s)
[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.
- 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.