Create Transaction LOG BACKUP Maintenance Plan in SQL Server

In this article, we will explain how to create Transaction LOG BACKUP Maintenance Plan as well as we will learn how to restore transaction Log Backup in SQL Server?

Applied To

• SQL Server 2017.
• SQL Server 2016.
• SQL Server 2012.

---

What’s Transaction LOG BACKUP?

Each database has two main types of files,

• Data file, and
• Transaction log file.

The transaction log is considered a critical part of the database that logs and records all transactions and modifications made on the database. Therefore, if a system failure occurred, you can bring your database back to a stable state if you are taking a transaction log backup frequently.

The transaction log backup would help to

1. Minimize work loss risks by restoring the log file to a specific period of time to bring your database back to the latest stable state before system failure.
2. Reduce and truncate the transaction log file size and prevent it from filling.

You might also like to read The transaction log for database SharePoint_Config is full due to LOG_BACKUP

So, in this article, we will explain how to take a transaction log backup frequently by performing Transaction LOG BACKUP Maintenance Plan.

---

Transaction LOG BACKUP Maintenance Plan Prerequisites

Before we getting started to create Transaction LOG BACKUP Maintenance Plan, we should be first aware of the following:

• Log Backup is working with Full or bulk-logged recovery models. it’s not working with a simple recovery model.
• You must have a full database backup before performing a Log Backup to avoid this error BACKUP LOG cannot be performed because there is no current database backup in SQL Server.
• The frequent time to take a transaction log backup mainly depends on your RTO and RPO. however, taking a transaction log backup every 15 to 30 minutes might be enough.

Transaction LOG BACKUP Permissions Required

• To create and manage Maintenance Plans, the current account should have “sysadmin” fixed server role.
• To create a log backup, you will need the below permission:
  • “sysadmin” fixed server role.
  • “db_owner” and “db_backupoperator” fixed database roles.

---

How to Create LOG BACKUP Maintenance Plan in SQL Server?

In this section, we’ll show step by step how to create a transaction LOG BACKUP Maintenance Plan in SQL server using SQL Server Management Studio.

Steps

• Login to SQL Server.

• From Object Explorer > Management > Maintenance Plan Wizard.

• Click Next.

• Specify a meaning name > Click Change to Set Schedule.

• I recommend building a frequently schedule based on your requirement > OK.

• The Schedule has been assigned to the maintenance plan adequately  > Next.

• In Maintenance Tasks > Select Back up database Transaction log > Next.

• In the case of selecting more task, you could be able to specify their order here > Next.

• Define backup database task by choosing all database or a specific database.

• Specify the following:
  • The folder that should contain the automatically created database files.
  • The backup files extension. The default is .trn.
  • Check verify the backup integrity to make sure the backup has been completed successfuly and all volumes are readable.

• Specify the report path > Check email report if you need to receive a report. (Note: you should configure email setting in SQL to can send report by email)

• Click Finish to complete the maintenance plan wizard.

• Ensure the maintenance plan is created successfully > Close.

• Go back to Management section to ensure that the Maintenance plan has been created.

• Again, Go back to SQL Server Agent > Jobs > Check the job connected to the maintenance plan.

• To test the Maintenance Plan > Right click on Job and Start > The job should be now started and completed successfully.

• Go to the backup path where the backup log has been created successfully.

---

How to Restore transaction log backup in SQL Server?

In case, the system failure, you can restore the transaction log backup to a particulate time of point to bring your database back to a stable state through the following:

Steps

• Connect to your SQL Instance.

• From Object Explorer > Databases
• Click on the database that you would like to restore its log backup.
• Select “Tasks”, Restore > Transaction Log.

The transaction log is disabled in SSMS

• In case the Transaction log is disabled or grayed out, so you may need first to restore a full or differential backup.

Restore Full Database Backup with NORECOVERY

• Right Click on the Database that you would like to restore.
• Select Tasks > Restore > Database.
• In the “General” tab, specify the location of your database backup.

• In the “Options” tab, at “Recovery State“, select “RESTORE WITH NO RECOVERY“.

• Click Ok to perform restore with no recovery.

• Once the restore is done, you will note that the database is in the “Restoring” state.

Restore Transaction Log backup

• Right click on the database name, select “Tasks” > Restore > Transaction Log.
• Great, the Transaction Log is now enabled.

• In the Restore transaction log dialog, you should do the following:
• At “General”, specify the location of your Transaction log backups.

• At “Options”, make sure that you have checked “Restore With Recovery” if there are no additional log files to restore.

• Great, the transaction log backup has been restored successfully.

---

RESTORE LOG Backup using T-SQL

• Restore full / differential database backup with No Recovery

RESTORE 'Database_name' FROM DISK 'C:\\backup path' WITH NORECOVERY

Where “database_name” is the name of the database and “backup path” is the location of full backup that is being restored.

• After restoring the last backup, perform a Transaction Log Backup using T-SQL as the following

RESTORE LOG 'Database name'
FROM SDISK 'C:\\backup path'
WITH FILE = 1,
WITH NORECOVERY
GO

• Repeat the above step for each transaction log backup you need to apply.

RESTORE LOG 'Database name'
FROM SDISK 'C:\\backup path'
WITH FILE = 2,
WITH NORECOVERY
GO

• Finally, perform restore database backup with recovery

RESTORE 'Database_name' FROM DISK 'C:\\backup path'
WITH RECOVERY
GO

---

Conclusion

In conclusion, we have learned the following:

• What’s the Transaction log?
• What are the benefits of Transaction Log Backup?
• The permission required to perform Transaction Log Backup?
• How to create a Transaction Log Backup maintenance plan in the SQL Server.
• How to restore the Transaction Log using SSMS.
• How to restore the Transaction Log using T-SQL.

You might also like to read

• Restore Transaction Log grayed out in SQL Server 2017.
• Shrink a Transaction Log File Maintenance Plan in SQL Server 2016.
• The transaction log for database SharePoint_Config is full due to LOG_BACKUP.
• SQL Server: Get the Detailed Information Via SERVERPROPERTY.