SQL 2000 Database Maintenance Plan vs SQL Server 2005 SSIS Tasks

By:   |   Comments (8)   |   Related: > Maintenance


For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands.  The Database Maintenance Plan Wizard's point and click interface enables backups, DBCC's, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule.  So how is this accomplished in SQL Server 2005?


With SQL Server 2005 the new GUI tool to perform database maintenance is a scaled down version of SQL Server Integration Services (SSIS).  So when transitioning from SQL Server 2000 to 2005 it is necessary to spend some time getting familiar with the SSIS tools to continue to perform or enhance the SQL Server 2000 capabilities.  Below we will walk through the examples to transition between the two platforms.

SQL Server 2000 - Database Maintenance Plan Wizard

ID Directions Screen Shot
1 Access the Database Maintenance Plan Wizard - Open SQL Server 2000 Enterprise Manager and navigate to root | Server Name | Management | Database Maintenance Plans.  Then right click in the right pane and select the 'New Maintenance Plan...' to start the wizard.

Press the 'Next' button to begin the process.

MaintPlan 1


2 Select Databases - Determine the databases that you would like to include in the plan.

*** NOTE *** - This wizard can be run and saved for other databases and have different configurations.  Just be sure to not have conflicts in the configurations.

MaintPlan 2


3 Update Data Optimization Information - Configure the index and shrink parameters with the recurring schedule. MaintPlan 3
4 Database Integrity Check - Configure the database integrity configurations and recurring schedule. MaintPlan 4
5 Specify the Database Backup Plan - Configure the backups as a portion of the maintenance process and the backup location. MaintPlan 5
6 Specify Backup Disk Directory - Configure the backup directory, file extension and retention period. MaintPlan 6
7 Specify the Transaction Log Backup Plan - Determine if the transaction log should be a portion of the maintenance plan, the location and schedule. MaintPlan 7
8 Specify Transaction Log Backup Disk Directory - Specify the backup directory, file extension and retention period. MaintPlan 8
9 Reports to Generate - Configurations for the output report for the maintenance process. MaintPlan 9
10 Maintenance Plan History - Determine if this plan's history should be retained and the number of corresponding rows. MaintPlan 10
11 Completing the Database Maintenance Plan Wizard - Review the final configurations for the wizard and press the 'Finish' button to complete the process.  If you are unsatisfied with the configurations, press the 'Back' button to find the appropriate screen and change the parameters. MaintPlan 11
12 Review in SQL Server 2000 Enterprise Manager -  The Database Maintenance Plan Wizard will bring you back to the original folder in Enterprise Manager.  If you need to modify the plan in the future, just double click on it to access the configurations. MaintPlan 12

SQL Server 2005 - SQL Server Integration Services 

ID Directions Screen Shot
1 Starting SSIS's Maintenance Interface - To begin creating an SSIS Package to support your maintenance needs, open SQL Server 2005 Management Studio and login to the 'Database Engine'.  Navigate to root | Management | Maintenance Plans.  Then right click on the 'Maintenance Plans' folder and select the 'New Maintenance Plan' option.  Name the maintenance plan and the SSIS interface will load with a blank interface.

*** NOTE *** - You can have multiple SSIS Packages scheduled at different intervals to meet your needs.

SSISMaint 1
2 Job Schedule Properties - Configure the recurring schedule for the SSIS Package. SSISMaint 2
3 Manage Connections - Review the default connection and via the 'Add' button, add additional connections to the SSIS Package. SSISMaint 3
4 Reporting and Logging - Configure the SSIS Package logging parameters. SSISMaint 4
5 Back up Database Task - Configure Full or Differential backups for specified or all databases to be written to a sub directory. SSISMaint 5
6 Check Database Integrity Task - Verify the database and index integrity. SSISMaint 6
7 Rebuild Index Task - Rebuild and reorganize the indexes for all tables in the user defined databases. SSISMaint 7
8 Shrink Database Task - Shrink the databases and return the freed space to Windows. SSISMaint 8
9 Update Statistics Task - Update the statistics for all tables in the user defined databases. SSISMaint 9
10 Final SSIS Package - Review the final SSIS Package with all tasks executing based on a successful status. SSISMaint 10
Next Steps
  • As you begin to transition from SQL Server 2000 to 2005, keep in mind that the database maintenance will be migrating from a point and click interface to a feature rich development environment.
  • Be sure to spend time learning SSIS not only for the many new features to support business intelligence (BI) projects, but also to automate DBA related tasks.
  • If you would like to learn about the underlying T-SQL commands to perform SQL Server 2000 maintenance check out the SQL Server - Performing maintenance tasks tip.  Stay tuned for the related SQL Server 2005 tip in the coming weeks.
  • Check out the latest SQL Server maintenance tips on MSSQLTips.com.
  • Check out the latest SQL Server SSIS tips on MSSQLTips.com.
  • Stay tuned for more tips related to constraints, error handling, logging, disabling tasks, troubleshooting SSIS maintenance packages in some upcoming MSSQLTips.com tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 11, 2012 - 9:24:28 AM - Jeremy Kadlec Back To Top (16851)


I would issue a backup to the local disk and then copy the backup file to the central server.  You could use COPY, XCOPY MOVE or FTP to perform the copy.

Here is a tip on the backup command I would recommend checking out - http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/.

Here is a tip with some information on the XCOPY command - http://www.mssqltips.com/sqlservertip/1729/dos-commands-for-the-sql-server-dba/.


Thank you,
Jeremy Kadlec

Wednesday, April 11, 2012 - 7:08:49 AM - Vegeta Back To Top (16846)

Hi, wondering if its possible to backup the file at 2 locations in sql 2000. I mean I need a copy of backup to be saved on the local machine and also on the central network incase of any disk failure on the local machine. Any help would be appreciated.

Thursday, November 3, 2011 - 1:05:55 PM - Jeremy Kadlec Back To Top (15007)


Thank you for the feedback.  That is a good observation.  The intention of the tip was more about showing how the 2 tools differed, but you are correct about shrinking the databases.

When I get sometime I will re-write this tip and provide some additional commentary.

Thank you,
Jeremy Kadlec

Thursday, November 3, 2011 - 12:19:55 PM - tihay Back To Top (15006)


I have looked at the SSIS plan and I see that u perform a shrink database. Don't do a shrink database after a rebuild index. Shrinking a database will defrag all your indexes.


Wednesday, September 1, 2010 - 9:21:20 AM - Admin Back To Top (10106)

In general, I would recommend running integrity checks during maintenance windows.

Thank you,
The MSSQLTips Team

Wednesday, September 1, 2010 - 5:51:54 AM - SNIVAS Back To Top (10103)
Is it fine running Integrity check after backup Job ?

your SSIS pack is showing that...

Friday, May 9, 2008 - 2:45:30 PM - admin Back To Top (967)


Are you having a problem with the wizard or SSIS?

Thank you,
The MSSQLTips.com Team

Monday, April 21, 2008 - 1:54:29 PM - [email protected] Back To Top (899)

What about the file retention problem.  I have had issues with trying to resolve on how to tell it to keep files only older than 2 days

get free sql tips
agree to terms