In the previous article, I discussed the possible responses to a full transaction log and suggested How to avoid it in the future.
In this article, I will explain
- How to create a LOG_BACKUP Maintenance Plan in SQL Server that should be run on a regular basis or on demand via Microsoft SQL Server Agent.
- How to restore a LOG_BACKUP that have already taken via Maintenance Plan.
*Creating a LOG_BACKUP Maintenance Plan in SQL Server
- 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 <backup_device> WITH NORECOVERY.
Where database_name is the name of database and <backup_device>is the name of the device that contains the log backup being restored.
- After restoring the last backup in your restore sequence, to recover the database use 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.