Welcome to Lesson 6 of the SQL Server 2000 Tutorial. In this lesson we will cover maintenance plans. I hope you enjoy the lesson.

Maintenance Plans

Maintenance Plans

In the previous tutorial you learn how to do database backups on the fly.  However, backups are usually done one a regular basis and manually backing up the database everyday can be very tedious.  This is where you would create a maintenance plan. 

 

To create a maintenance plan, open up Enterprise Manager, navigate to the database, and click on the wizard button.

Select the Database Maintenance Plan Wizard and click OK.

Click Next.

Select the database you want to backup on a regular basis.  In our case we will backup the Northwind database only.  (Note: you should not backup the system databases (master, model, and msdb on the same maintenance plan.  Use a separate plan for the system databases.)

You can tell SQL Server to do data optimization in your maintenance plan also.  We will skip this in the interest of time.  Click Next.

You can tell SQL Server check the database for integrity in your maintenance plan also.  We will skip this in the interest of time.  Click Next.

Click Next.

You can specify how often you database is backed up.  We will select that we want the database to be backed up Monday through Friday at 10 PM.  Click OK when done. 

The wizard redisplay what you selected.  Click Next.

You can specify where to store your backups.  We leave it at the default location and click Next.

You can backup the transaction log if you need.  Click Next.

Click Next.

 

Give the Maintenance Plan a name that is descriptive such as Northwind Nightly Backup.  Click Finish.