Create a LOG_BACKUP Maintenance Plan in SQL Server

In this article, I will explain

  1. How to create LOG_BACKUP Maintenance Plan in SQL Server?
  2. How to restore LOG_BACKUP that have already taken via Maintenance Plan?

maintinance plan wizard 10


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:

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 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


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.

restore multiple back up


Applies To
  • SQL Server 2008.
  • SQL Server 2012, 2014.
  • SQL Server 2016, 2017.
See also
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s