In this article, I will explain
- How to create LOG_BACKUP Maintenance Plan in SQL Server?
- How to restore LOG_BACKUP that have already taken via Maintenance Plan?
Keep in mind
Before you getting started, you should be aware of
- Backup Log is working with Full or bulk-logged recovery models it’s not working with simple recovery model.
- 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.
- 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.
Creating a LOG_BACKUP Maintenance Plan in SQL Server.
To create a LOG_BACKUP Maintenance Plan in SQL server, you should follow the below 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.
- 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.
Restore a LOG_BACKUP
Execute the RESTORE LOG statement to apply the transaction log backup, specifying:
- The name of the database to which the transaction log will be implemented.
- The backup device where the transaction log backup will be restored from.
- The NORECOVERY clause.
The basic syntax for this statement is as follows:
RESTORE LOG database_name FROM WITH NORECOVERY
Where “database_name” is the name of the database and “” is the name of the device that contains the log backup that is being restored.
After restoring the last backup in your restore sequence, to recover the database using the following statement:
RESTORE database_name WITH RECOVERY
Example: Restoring a single transaction log backups using T-SQL
RESTORE DATABASE SharePoint_Config FROM SharePoint_Config_1 WITH NORECOVERY GO RESTORE LOG SharePoint_Config FROM SharePoint_Config_log WITH FILE = 1, WITH NORECOVERY GO RESTORE DATABASE SharePoint_Config WITH RECOVERY GO
Example: Restoring Multiple transaction log backups using SSMS.
- SQL Server 2008.
- SQL Server 2012, 2014.
- SQL Server 2016, 2017.
- How to Shrink a transaction log file via a Maintenance Plan in SQL Server.
- SQL Server 2016: Create Log Backup Maintenance Plan.