SQL Server 2016: Create Log Backup Maintenance Plan
Introduction
In this article, we will explain How to take log backups frequently by creating a Log Backup Maintenance Plan in SQL Server 2016?
Consideration
Before you begin, you should be aware of the following
- To create and manage Maintenance Plans, it requires being a member of the sysadmin fixed server role.
- To create a Backup Log, it requires being the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
- As a mandatory prerequisite, you must have a full database backup before performing a Backup Log to avoid this error: BACKUP LOG cannot be performed because there is no current database backup.
- Depending on the size of the databases and the amount of data transaction, you could end up needing quite a lot of disk space for the log backups.
- Backup Log is working with Full or bulk-logged recovery models.
- If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup.
Note
- Performing a log backup will free the space within the transaction log to be reused for new log records.
- If the log backup is not taken, the transaction log will continue to grow that lead to
- Filling up the Disk Drive rapidly.
- Getting (9002) error "The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP"
Steps
To keep the log from filling up, you should take log backups frequently by creating a Log backup maintenance plan as the following:
- Connect to SQL Server.
- From Object Explorer > Management > Maintenance Plan Wizard.
- The Maintenance Plan Wizard should be shown, click Next.
- Set the Maintenance Plan > click Change to Set Schedule.
- Set the Schedule setting as you prefer, click OK then Next.
- In Maintenance Tasks > select Back up database Transaction log > Next.
- In Task Order, we only have one task, click Next.
- On General tab, click the Database(s) drop-down list.
- Select the desired database, or select all databases based on your requirement.
- In Destination tab, specify the folder that should contain the automatically created database files. Specify the backup files extension. (The default is .trn)
- On the Options tab, check "verify the backup integrity" to make sure the backup has been completed successfully and all volumes are readable.
- Specify the report path > check email report if you need to receive a report by email.
- (Note: you should configure email setting in SQL to can send the report by email).
- Click Finish to complete the Maintenance Plan Wizard.
- The maintenance plan has been created successfully, click Close.
- To test and execute the Maintenance Plan, right-click the created maintenance plan > select Execute
- Go to the backup path where the backup log has been created successfully.
Applies To
- SQL Server 2017
- SQL Server 2016
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008
Conclusion
In this article, we have explained How to Create a Log Backup Maintenance Plan in SQL Server 2016.