Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL 2000 Database Maintenance Plan vs SQL Server 2005 SSIS Tasks


By:   |   Read Comments (8)   |   Related Tips: More > Maintenance

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem
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?

Solution
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.

 

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.

 

3 Update Data Optimization Information - Configure the index and shrink parameters with the recurring schedule.
4 Database Integrity Check - Configure the database integrity configurations and recurring schedule.
5 Specify the Database Backup Plan - Configure the backups as a portion of the maintenance process and the backup location.
6 Specify Backup Disk Directory - Configure the backup directory, file extension and retention period.
7 Specify the Transaction Log Backup Plan - Determine if the transaction log should be a portion of the maintenance plan, the location and schedule.
8 Specify Transaction Log Backup Disk Directory - Specify the backup directory, file extension and retention period.
9 Reports to Generate - Configurations for the output report for the maintenance process.
10 Maintenance Plan History - Determine if this plan's history should be retained and the number of corresponding rows.
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.
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.

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.

2 Job Schedule Properties - Configure the recurring schedule for the SSIS Package.
3 Manage Connections - Review the default connection and via the 'Add' button, add additional connections to the SSIS Package.
4 Reporting and Logging - Configure the SSIS Package logging parameters.
5 Back up Database Task - Configure Full or Differential backups for specified or all databases to be written to a sub directory.
6 Check Database Integrity Task - Verify the database and index integrity.
7 Rebuild Index Task - Rebuild and reorganize the indexes for all tables in the user defined databases.
8 Shrink Database Task - Shrink the databases and return the freed space to Windows.
9 Update Statistics Task - Update the statistics for all tables in the user defined databases.
10 Final SSIS Package - Review the final SSIS Package with all tasks executing based on a successful status.

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Vegeta,

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/.

HTH.

Thank you,
Jeremy Kadlec


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

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 03, 2011 - 1:05:55 PM - Jeremy Kadlec Back To Top

tihay,

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 03, 2011 - 12:19:55 PM - tihay Back To Top

Hi,

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.

regards,


Wednesday, September 01, 2010 - 9:21:20 AM - Admin Back To Top
SNIVAS,

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

Thank you,
The MSSQLTips Team


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

your SSIS pack is showing that...


Friday, May 09, 2008 - 2:45:30 PM - admin Back To Top

tcronin,

Are you having a problem with the wizard or SSIS?

Thank you,
The MSSQLTips.com Team


Monday, April 21, 2008 - 1:54:29 PM - tcronin@cmsstl.com Back To Top

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


Learn more about SQL Server tools