Create a database maintenance plan from scratch
You’ll find Maintenance Plans under the Management tab in the server object explorer window in SQL Server Management Studio. (Figure A) Right-clicking the folder gives you the option of creating a new maintenance plan from scratch or creating one via the wizard. Select the New Maintenance Plan option to create one from scratch. (Figure B)
Creating a new maintenance plan brings up a familiar looking SSIS interface with several maintenance plan tasks. These tasks are also available for use in regular SSIS projects.
As part of my maintenance plan, we want to create a full backup of all of my all databases on one database instance. The Backup Database task will be the task we will use to accomplish this.
The Backup Database task gives us the option to back up: all databases on the instance; all system databases; all user databases; or specific databases. For this maintenance plan,
We want to back up all databases on the instance. (Figure C And D)
Below is the Backup Database properties window. Our plan is to make a full backup of all databases and place the .bak files on C:\SQLDatabaseBackup directory.
We want to rebuild the indexes in them. These tasks can be interchangeable in terms of the order in which they execute. We can back up the databases before the rebuild so that we know we have a good working backup copy of the databases in case some tragic error occurs during the rebuild process.
Rebuilding indexes resorts and defragments indexes on database tables for views to improve their efficiency when sorting or searching. Rebuild Index Task will be the task we will use to accomplish this (Figure E).
Sort results in tempdb
For an index to retrieve results effectively, it must remain in sorted order. When an index is rebuilt, it must resort the data in the index. This resorting of data is typically done in the database in which the index resides. You now have the option to sort these indexes in the tempdb database; this has advantages and drawbacks. If the tempdb on my system is on a different set of disks than my user databases, it may be quicker to sort the index in the tempdb database; however, this requires the index rebuild to use more disk space. If space is not an issue for your system, it might not be a bad idea to play around with this option to see if it speeds your rebuild time.
Below is my almost complete maintenance plan. Notice the red failure precedence from the Backup Database Task and Rebuild Index Task to the Send Alert task. This precedence will cause my Send Alert task to execute only if one of the tasks encounters a failure. (Figures F and G)
All that is left to do is schedule the maintenance plan so that it will run.
We want our backups to run daily at midnight, and we want the SQL Agent job to run daily at midnight. (typically like to run full backups and rebuild indexes during off-peak hours of the day.)
Microsoft SQL Server 2005 Integration Services website: http://www.microsoft.com/sqlserver/2005/en/us/integration-services.aspx