Create a LOG_BACKUP Maintenance Plan in SQL Server

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

  1. 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.
  2. How to restore a LOG_BACKUP that have already taken via Maintenance Plan.

Steps

*Creating a LOG_BACKUP Maintenance Plan in SQL Server

  • 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

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

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

restore multiple back up

 

Enjoy 🙂

See also How to Shrink a transaction log file via a Maintenance Plan in SQL Server

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s