Support: 1-800-961-4454
1-800-961-2888

When Disaster Strikes: Creating MSSQL Maintenance Plans

Resource for this article: John Denman, Rackspace Support

You’ve likely experienced the panic of a computer crashing. In the midst of writing a sales proposal or developing a corporate presentation the screen goes blank. This loss of data can be detrimental. Compound that, though, and think what would happen if your entire e-commerce database crashed. However, if you had a consistent, thorough maintenance plan in place, disaster recovery would be a simple process.

Fortunately, MSSQL maintenance plans can be easily setup within the SQL Server Management Studio. The Setup Wizard is a quick, step-by-step process for establishing maintenance plans. Maintenance Plan Designer (not outlined in this article), a manual setup process, is also available and will allow for a higher degree of fine-tuned customizations.

Combining a series of full backups with differential backups and optimization techniques is a proactive solution to ensure your data is recoverable with disaster strikes. These backups and tasks will not only secure data, but will also keep your databases running at top efficiency – helping to decrease query retrieval time for applications.

Maintenance Plan Setup: Full Backup

Full backups contain the entire content of the database. Since these backups are slower and require quite a bit of disk space for larger databases, it’s recommended to perform these once a week, at an off-hour during the weekend.

1) Load SQL Management studio

Start -> Programs -> Microsoft SQL Server 2008 – > SQL Server Management Studio

2) Connect to database instance using windows Authentication

3) Open Database folder

4) Select Management -> Maintenance Plan and right click, select Maintenance Plan Wizard

5) Select Next

6) Title the plan (in this instance, “Full BackUp”) and select “Change” button

7) This full backup dialogue box allows you to control frequency (day/time) – select “OK” when done

8.) Screen will close, select “Next”

9) Check the Box next to “Back Up Database (Full)”

10) Click “Next” twice

11) Select the databases to backup and the folder in which the backups will be stored. Also check the following boxes:

a. Create subdirectory for each database (it’s important to keep backups organized on file system)
b. “Verify Backup Integrity”

12) Click “Next” Twice

13) Finish

14) Close. The plan should now be listed under “Maintenance Plan” folder – this is where you can adjust schedule or update any of the settings established already.

Maintenance Plan Setup: Differential Backup Plan

Differential backups contain only the data that has changed since the full backup. These backups are often faster and do not require as much disk space. In order to restore a differential backup you will first need to restore the last full backup.

1) Right click Maintenance Plan, select Maintenance Plan Wizard

2) Select Next

3) Title Plan (in this instance, “Differential BackUp”) and select “Change” button

4) This full backup dialogue box allows you to control frequency (day/time). The differential backup should be run on all days that the full back up is not running. Click “Ok” when time/days have been established.

5) Click Next

6) Check the box next to “Backup Differential”

7) Click “Next” twice

8.) Select databases for which the differential backup will be performed (these will be the same databases as the full backup).Also check the following boxes:

a. Create subdirectory for each database (it’s important to keep backups organized on file system)
b. “Verify Backup Integrity”

9) Next -> Next -> Finish -> Close

10) The backup plan has been added under Maintenance Plans – this is where you can adjust the scheduling or update any of the task settings.

Maintenance Plan Setup: Remove Old Backup Files

It’s important to create a maintenance plan to cleanup old files like those generated by previous backups. Without a maintenance plan to clean up these files, your backup maintenance plan will continue to generate files and take up valuable disk space.

1) Right click Maintenance Plan, select Maintenance Plan Wizard

2) Select Next

3) Title Plan (in this instance, “Clean Up Backup Files”) and select “Change” button

4) Schedule this to run daily—perhaps in the evening or off-hours. Click “Ok” when time/days have been established.

5) Click “Next”

6) Check the box next to “Maintenance Cleanup Task”

7) Click “Next” twice

8.) Paste folder extension where all backups are stored and set file extension to “bak”; select box for “Include first-level subfolders”

9) Configure to retain two weeks worth of backups (this may vary for your individual backup needs)

10) Next -> Next -> Finish -> Close

11) The backup plan has been added under Maintenance Plans – this is where you can adjust the scheduling or update any of the task settings.

Maintenance Plan Setup: Optimization & Maintenance Plans

Another important step in database maintenance is regular optimization through tasks like reorganizing the data and index pages, compressing the databases, updating index statistics and performing consistency checks. These tasks will ensure that your database is efficient and error-free. Like the other plans, this plan should be scheduled during late evening hours since it will affect the database engine performance.

1) Right click Maintenance Plan, select Maintenance Plan Wizard

2) Select Next

3) Title Plan (in this instance, “Weekly Maintenance”) and select “Change” button

4) Schedule this to run weekly during off-hours; Click “OK”

5) Click Next

6) There will be four maintenance task boxes checked: Integrity Task, Shrink Database Task, Index Task and Statistics Task

7) Click Next

8.) Select databases for Integrity Task -> OK -> Next

9) Select databases for Shrink Database Task -> OK -> Next

10) Select databases for Reorganize index Task -> OK -> Next

11) Select databases for Update Statistics Task -> OK -> Next

12) Next -> Next -> Finish -> Close

13) The backup removal plan has been added under Maintenance Plans – this is where you can adjust the scheduling or update any of the task settings.

About the Author

This is a post written and contributed by Ashleigh Davis.


More
Racker Powered
©2014 Rackspace, US Inc.