support  |  log in
learning center  >  BLOG POSTS  >  Automated Backup Routine

Blog Posts

Creating an Automated Backup Routing

Microsoft SQL Server has the ability to create database backups (or collection of databases) automatically without human intervention.  There are many benefits to implementing a backup strategy using SQL Servers automation tools.  Chief among these benefits are:

·         Data loss prevention:  If implemented on an automated & recurring schedule, a proper backup strategy provides database administrators, in the event of a system crash, the ability to restore data with minimal loss.  It even creates the potential for “point-in-time” restores, allowing an administrator to restore and/or revert a database to a specific hour and minute of a particular day.

·         Limit transaction log growth:  SQL Server writes every Insert, Update, & Delete statement (and other transactions as well) to the transaction log (i.e. “database_log.ldf” file(s)).  If the database is set to use a Full or Bulk-logged recovery model, this data is retained indefinitely.  Without frequent backups, the transaction log can grow to a gargantuan size and consume massive amounts of hard disk space.  If it is never backed up, it will eventually: consume all the space on the hard disk/partition on which it resides, cause the database to enter a “read only” state and result in all inserts, updates, and deletes to be roll backed and cancelled.

·         Eliminate backup file accumulation:  When coupled with the use of a SQL Server Backup Device(s), the backups will use the same file repeatedly and the administrator will not need to worry about the accumulation of many backup files and the routine archiving and/or deleting that having such files entails.

This document is meant to provide recommended or “standard” practices in order to achieve the aforementioned benefits.  It is by no means meant to be the only solution or steps to which a site and/or customer may achieve them.  A proper backup strategy is designed around the environment and needs of the site and/or customer.  For more information about creating a SQL Server backup strategy, click here.

NOTE: Al the steps given this document will be done in MS SQL Server Management Studio, unless specified otherwise. 

Automating MS SQL Server Backups

In order for the steps discussed below to work, the following prerequisites must be met:

·         SQL Server service

Configured to run as an account with appropriate NTFS permission to the read and write data in the directory where the backup files will be located.

·         SQL Server Agent service

Must be running.

·         Configured to run as an account with appropriate NTFS permission to the read and write data in the directory where the backup files will be located.  Microsoft recommends using the same account that the SQL Server service is running as. 

Step 1: Choose & Create the Backup Directory

The first step is to decide where you want to store the backup, usually, a directory on a local drive/partition, but can also be a UNC path to a network storage device.  Again, whatever the backup directory location you decide on, you need to make sure the following statements are true:

·         The location actually exists.  If the folder you wish to use doesn’t exist, you must create it.

·         The windows account that the SQL Server and SQL Server Agent services are running as has sufficient read/write permissions to the desired location. 

Step 3: Maintenance Plan

Create the Maintenance plan

1.     Expand the Management folder

2.     Right click on Maintenance Plans and select New Maintenance Plan…

3.     In the New Maintenance Plan window, type “Backup Plan” in the Name field.

 

Create the Weekly Full Backup Subplan

1.     Double click the row that says “Subplan_1” in the Subplan column.

2.     In the Subplan Properties window:

a. Replace the value in the Name field with “Weekly Full backup”.

a.     Replace the value in the Description field with “Perform full backup and rebuild indexes each Sunday”.

b.     Click OK.

2.     Click the calendar button the row that says “Full backup” in the Subplan column.

3.     In the Job Schedule Properties – Backup Plan.Full backup window:

a.     Choose “Recurring” in the Schedule Type list.

b.     Check the Enabled box.

c.     Select “Weekly” within the Occurs list.

d.     Enter 1 in the Recurs every field.

e.     Check only the Sunday box.

f.      Type “12:05:00 AM” in the Occurs once at field.

g.     Enter today’s date in the Start date field.

h.     Select No end date.

i.      Click OK.

5.   Drag Back Up Database Task from the Toolbox to the designer surface (the large grey area below the Subplan list).

6.   Double click the dotted border that surrounds the Back Up Database Task.

7.   In the Back Up Database Task window:

a.     Select Full in the Backup Type list.

b.     Select the database you are creating the maintenance plan for within the Database(s) list.

c.     Select the Database option for the Backup Component.

d.     Select the Disk option for Back up to:

e.     Select Back up databases across one or more files:

f.      Click Add…

g.     In the Select Backup Destination window.

i. Select the Backup Device option. ii. Select BackupDevice1a from the list.

iii. Click OK.

h.     Repeat steps f and g except choose BackupDevice1b from the list.

i.      Select Overwrite in the If backup files exist: list.

j.      Check the Verify backup integrity box.

k.     Click OK.

8.   Drag Rebuild Index Task from the Toolbox to the designer so that it is below Back Up Database Task.

9.   Single click the Back Up Database Task.

10. Drag the green arrow underneath Back Up Database Task so that it connects Back Up Database Task to Rebuild Index Task.

11. Double click the dotted border that surrounds the Rebuild Index Task.

12. In the Rebuild Index Task window:

a.     Select the database you are creating the maintenance plan for within the Database(s) list.

b.     Select Tables and Views in the Object: list.

c.     Select the Change free space per page percentage to: option and enter “70” in the percentage box.

d.     Check the Keep index online while re-indexing box.

e.     Click OK.

 

Create the Nightly Full Backup Subplan

1.     Click on Add Subplan.

2.     In the Subplan Properties window:

a.     Replace the value in the Name field with “Nightly Full backup”.

b.     Replace the value in the Description field with “Perform Full backup nightly” c. Click OK.

3.     Click on the calendar button on the row that says “Nightly Full backup” in the Subplan column.

4.     In the Job Schedule Properties – Backup Plan.Nightly Full backup window:

a.     Choose “Recurring” in the Schedule Type list.

b.     Check the Enabled box.

c.     Select “Weekly” in the Occurs list.

d.     Enter 1 in the Recurs every: field.

e.     Check the Monday, Tuesday, Wednesday, Thursday, and Friday boxes.

f.      Type “11:55:00 PM” in the Occurs once at: field.

g.     Enter today’s date in the Start date field

h.     Select No end date.

i.      Click OK.

5.     Drag Back Up Database Task from the Toolbox to the designer surface.

6.     Double click the dotted border that surrounds the Back Up Database Task.

7.     In the Back Up Database Task window:

a.     Select Full in the Backup Type list.

b.     Select the database you are creating the maintenance plan for within the Database(s) list.

c.     Select the Database option for the Backup Component.

d.     Select the Disk option for Back up to:

e.     Select Back up databases across one or more files:

f.      Click Add…

g.     In the Select Backup Destination window.

i. Select the Backup Device option. ii. Select BackupDevice1a from the list.

iii. Click OK.

h.   Repeat steps f and g except choose BackupDevice1b from the list.

i.     Select Append in the If backup files exist: list.

j.     Check the Verify backup integrity box.

k.    Click OK.

 

Create the Transaction Log backup Subplan

1.     Click Add Subplan.

2.     In the Subplan Properties window:

a.     Replace the value in the Name field with “Tran Log backup”.

b.     Replace the value in the Description field with “Perform Transaction Log backup every 6 hours”.

c.     Click OK.

3.     Click the calendar button on the row that says “Differential backup” in the Subplan column.

4.     In the Job Schedule Properties – Backup Plan.Tran Log backup window:

a.     Choose “Recurring” within the Schedule Type list.

b.     Check the Enabled box.

c.     Select “Daily” in the Occurs list.

d.     Select the Occurs every: option and set it to run every 6 hours, Starting at: 6:00:00 AM and Ending at: 7:00:00 PM.

e.     Enter today’s date in the Start date field.

f.      Select No end date.

g.     Click OK.

5.     Drag Back Up Database Task from the Toolbox to the designer surface.

6.     Double click the dotted border that surrounds the Back Up Database Task.

7.     In the Back Up Database Task window:

a.     Select Transaction Log within the Backup Type list.

b.     Select the database you are creating the maintenance plan for in the Database(s) list.

c.     Select the Database option for the Backup Component.

d.     Select the Disk option for Back up to:

e.     Select Back up databases across one or more files:

f.      Click Add…

g.     In the Select Backup Destination window.

i. Select the Backup Device option. ii. Select BackupDevice1a from the list.

iii. Click OK.

h.   Repeat steps f and g except choose BackupDevice1b from the list.

i.     Select Append in the If backup files exist: list.

j.     Check the Verify backup integrity box.

k.    DO NOT check the Backup the tail of the log box.

l.     Click OK.

8.   Save your maintenance plan by either clicking the Save Button in the toolbar or typing Ctrl-S.

 

Other Unique Sites

 

Unique.net 

 

Cairs Blog 

 

FTP Site

 

 

                          

 

 

Unique.net Pages

 

Our Products

 

About Us

 

Support

 

Cairs Blog

 

Contact Us

 

Learning Center Pages

 

Learning Center

 

Role-Based Training

 

Training-Courses

Support

 

Contact Us

 

Report a Trouble

 

Request a Feature

 

 

 

© Unique Communication Solutions 2012