Create Transaction LOG BACKUP Maintenance Plan in SQL Server

Transaction LOG BACKUP Maintenance Plan

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.

Create Transaction LOG BACKUP Maintenance Plan in SQL Server
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:

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.
Connect to SSMS
  • From Object Explorer > Management > Maintenance Plan Wizard.
maintinance plan wizard
  • Click Next.
maintinance plan wizard 1
  • Specify a meaning name > Click Change to Set Schedule.
maintinance plan wizard 2
  • I recommend building a frequently schedule based on your requirement > OK.
New Job Schedule
  • The Schedule has been assigned to the maintenance plan adequately  > Next.
maintinance plan wizard 3
  • In Maintenance Tasks > Select Back up database Transaction log > Next.
maintinance plan wizard 4
  • In the case of selecting more task, you could be able to specify their order here > Next.
maintinance plan wizard 5
  • Define backup database task by choosing all database or a specific database.
maintinance plan wizard 6
  • 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.
maintinance plan wizard 7
  • 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)
maintinance plan wizard 8
  • Click Finish to complete the maintenance plan wizard.
maintinance plan wizard 9
  • Ensure the maintenance plan is created successfully > Close.
maintinance plan wizard 10
  • Go back to Management section to ensure that the Maintenance plan has been created.
maintinance plan wizard 16
maintinance plan wizard 11
  • Again, Go back to SQL Server Agent > Jobs > Check the job connected to the maintenance plan.
maintinance plan wizard 12.png
  • To test the Maintenance Plan > Right click on Job and Start > The job should be now started and completed successfully.
maintinance plan wizard 13
  • Go to the backup path where the backup log has been created successfully.
maintinance plan wizard 14.png
maintinance plan wizard 15

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.
Connect to SSMS
  • 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 Transaction Log Backup is disabled

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.
Full database Restore with no recovery in SSMS
  • In the “Options” tab, at “Recovery State“, select “RESTORE WITH NO RECOVERY“.
Restore Database with recovery state no recovery
  • Click Ok to perform restore with no recovery.
Database Restored successfully
  • Once the restore is done, you will note that the database is in the “Restoring” state.
Database in Restoring state

Restore Transaction Log backup

  • Right click on the database name, select “Tasks” > Restore > Transaction Log.
  • Great, the Transaction Log is now enabled.
Enable Transaction log option during restore
  • In the Restore transaction log dialog, you should do the following:
  • At “General”, specify the location of your Transaction log backups.
Restore Transaction log backup to point in time
  • At “Options”, make sure that you have checked “Restore With Recovery” if there are no additional log files to restore.
Leave the database ready to use by rolling back uncommitted transactions-min
  • Great, the transaction log backup has been restored successfully.
Restore transaction log backup done

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

2 thoughts on “Create Transaction LOG BACKUP Maintenance Plan in SQL Server”

  1. Pingback: SharePoint 2019: SQL Server Recommendations | SPGeeks

  2. Pingback: Auditing in SQL Server | SPGeeks

Leave a Reply