Search
Inforica India Main Banner
You are here: COMMUNICATION  >  Blog
The Inforica Infospace
Sep 20

Database Maintenance Plan for SQL Server

 

Introduction

Data management is one of the essential database maintenance tasks. Having a good backup plan in place helps organizations to recover the data from any failure in a very efficient way. In order to recover from a failure, organizations have to restore a set of backups in a logically correct and meaningful sequence. In SQL Server 2008 Microsoft redesigned the method through which Database Maintenance Plans are created and managed through a wizard in SQL Server Management Studio (SSMS).

Target audience

This document is intended as a guide  for SQL Server database administrators (DBAs), data protection architects, backup administrators and whoever is seeking information on how to take database backup and restore using SQL Server Management Studio(SSMS). In order to go through this document, one should be familiar with SSMS, Creating databases and SQL server job agent.

Scope of this document

This document covers the steps involved in creating database’s back up and restoring it back on to database server.  This document is covering the database backup and restoration methods applicable to Microsoft SQL Server 2008 and may not be applicable to other databases servers from other vendors.

Maintenance plans in SQL server 2008

Purpose of a maintenance plan

The purpose of creating SQL Server backups is to enable you to recover a damaged database. However, backing up and restoring data must be customized to a particular environment and must work with the available resources. Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.

Types of back up

·         Database backups

o   Full Backup – This is a full backup of entire database and represents the state of the database at the point when the backup is completed.

o   Differential Backup – This is a backup of all the files within the database, and contains only the extents modified since the most recent full backup of each file. Restoring a database protected with full and differential backups to the most recent point in time includes recovering the most recent full and differential backup.

·         Transaction log backup

o   This backup contains all log records that have been backed up previously and can be associated with the database while restoring.


 

Create a backup maintenance plan

Connect to database engine and locate the “Maintenance plans” option under the Management folder as shown below

Right click on “Maintenance plans” and select “Create New Maintenance plan” option as shown below

 


 A popup box appears, where in key in the name of the maintenance plan. For example, type in “DatabaseBackupPlan”

 

Once, you have typed the maintenance plan name, click OK, The following window appears

 

This window has a toolbox on bottom left side, which contains the various tasks related to databases and a design area. A maintenance plan can have multiple subplans and each subplan will carry out a specific task. So, create three subplans to carry out the following tasks

1.       Take full back up of database

2.       Take differential back of database

3.       Take backup of transaction logs

Each of these tasks is separate from each other and will be scheduled to run on different time interval.


 

FULL BACKUP

By default, one subplan is created. To modify that, double click on that “subplan_1“and the following popup box appears

Provide meaning full name and description and then click on the button given beside the Schedule text box, which opens the Job Schedule Properties window as given below

This window provides the options to set up the running date and time for the job. Most important options are:

o   Schedule Type: Indicates if this job is recurring of one time

o   Frequency:  Job can be scheduled to run on Weekly, Daily or Monthly basis

o   Duration: Start and End date of the job

After selecting desired time and date option, click OK on job schedule propertied box and also click OK on Subplan properties pop up box.


Now, drag and drop the “Database Backup Task” from toolbox to design area. This shows up like this.

 

Double click on the task and the following popup box appears

In this window, the following options should be configured:

o   Connection: Which database server to use, by default it points to the local database server.

o   Backup Type: Three types are there, FULL, DIFFERENTIAL and TRANSACTION LOG. Select FULL.

o   Databases(s): Click on the drop down list and the following popup comes

Select the appropriate database and click on OK.

o   Back up to: Select DISK radio button and specify the location where database backup will be stored and click on OK button.

 Now if you notice, the database backup task looks like this

Selected database, type and destination are all set.


 

DIFFERENTIAL BACKUP

To add a new subplan, Click on Add Subplan option available on the top of Maintenance Plan window and repeat all the steps of FULL BACKUP

Notes:                                                                                                                                                                                  

  • Please ensure that the job schedules are selected as below so that simultaneous execution of weekly full database backup and daily differential backup can be avoided


  • Database Backup Task should be configured as below


 

TRANSACTION LOG BACKUP

To add a new subplan, Click on Add Subplan option available on the top of Maintenance Plan window and repeat all the steps of FULL BACKUP

Notes:

  • Please ensure that the job schedules are selected as below

 

  • Database backup task should be configured as below:

 

Once, all three subplans are created, maintenance plan will look like as given below

 

 

And 3 new jobs are created in SQL Server job agent.

 

 

Once, these jobs are created and running, the database backup files will be created under the directory specified in DATABASE BACKUP TASK, which may look like this

 

Naming convention of backup files

o   For database backup file

_ backup_.bak

o   For transaction log backup

_backup_<DateTime>.trn

 

Restore database from a full backup

 

In order to restore the database, follow the below mentioned order

FULL BACKUP >> TRANSACTION LOGS BACKUP >> DIFFERENTIAL BACKUP >> TRANSACTION LOGS BACKUP

Each backup is created with a date time stamp, so ensure that these files are restored in the same order. First, restore the FULL database backup file and then followed by the transaction log or differential backup file.  Restoration steps are given below:

1.       Connect to database engine and right click on database folder and select “Restore Database…” option

2.       In the Restore Database window, select the following options in General Tab

o   To Database: Specify the database, it can be an existing database of new database

o   Specify the source the location of backup sets to restore: select the location of database backup file

o   Select the backup set to restore: Select the check box of selected backup file

o   At this point the Restore Database window appears as below

3.       Now, move to the Options tabs and set the following options

o   Restore Options:  select multiple options based on your requirement

o   Restore the database files as:  Select the location for MDF and LDF files.

o   Recovery State: select the second radio button so that additional backup files  can be restored on this database . RESTORE WITH NON RECOVERY.

o   After above selection, window appears like this

 

4.       After the above selection, click on OK button and successful restoration message comes.

 

NOTE: At this point, restored database is not operational.  Expand the database folder in SSMS and the status of database will be shown like this

 

 

Restore database to a historical point using transaction logs

 

In order to make it operational, attach at least one transaction log backup by following the steps as mentioned in RESTORE FULL DATABASE BACKUP and make below mentioned changes

1.        In step 2, select the transaction log backup file( extension of file will be .trn) not the .bak file(screenshot is for illustration purposes)

2.        In step 3, Recovery state should be selected as RESTORE WITH RECOVERY (screenshot is for illustration purposes)

 

Search the blog
Latest Posts
Minimize
Overview of Pharma Industry
Database Maintenance Plan for SQL Server
Energy Waste Analysis
Home  |  CONTACT US Privacy Statement  |  Terms Of Use Inforica (India) Private Limited