If you are managing a SQL Server you will sooner or later need to schedule some maintenance tasks, like backing up your databases. The easy way to do this is use the SQL Server 2008 Maintenance Plan wizard (SSMPW). Here are some FAQs about SSMPW:
Ø How do I start the SSMPW?
o Open SQL Server Management Studio.
o Connect to the appropriate SQL Server instance.
o Navigate to Management | Maintenance Plans.
o Right click on Maintenance Plans and choose the option: Maintenance Plan Wizard.
o You should now be in the Introductory Menu of the SSMPW.
Ø What actions can I perform via using the SSMPW?
o Check database integrity
This option executes the DBCC CHECKDB command for one or more of your databases and it makes sure there are no allocation or consistency errors in your database.
You should be periodically executing database integrity checks, ideally once per day or at least once per week.
o Shrink Database
This option frees disk space which has been reserved by SQL Server for future usage, but is not currently used by SQL Server.
Unless you badly need back this space, or you are convinced that this space is never going to be used by the database in the future, it is better to not shrink the database, because otherwise you are only wasting I/O resources.
o Reorganize Index
Indexes are used by the SQL Server database engine to efficiently retrieve specific rows from SQL Server tables. Indexes are fragmented when you modify or insert rows in a table, so a reorganization is essential.
Ideally this should be done every day or at least once per week.
o Rebuild index
Same as above, but in this case the index is dropped and then rebuilt. Usually rebuilding yields better results than reorganizing indexes.
o Update statistics
Statistics are used by the SQL Server database engine to create execution plans so as to be more efficient when performing an operation like retrieving data from a table. As tables are modified, the statistics are getting less and less accurate.
Statistics are by default getting automatically updated in day-to-day operations, but if you want to be on the safe side, better schedule a periodical update.
o Clean up history
As SQL Server Books online say, “the History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations. This wizard allows you to specify the type and age of the data to be deleted.”
o Execute SQL Server Agent job
What this option does, is to let you specify a custom SQL Server agent job you have already created, to run as part of the Maintenance Plan.
e.g. you may want to truncate a table every weekend, so you may add this job as part of the maintenance plan to have a better overview of all the maintenance actions you have to do and if they are successful or not (you may not notice a SQL server agent that failed, but you will notice a Maintenance Plan that has failed steps).
o Backup Database (Full)
Here you can specify one or more databases to backup. Both the data files and the transaction log files will be backed up.
o Backup Database (Differential)
Differential Backup only works if you have previously captured a Full Backup. As the name implies this backup operation captures only the data which have been added, removed or modified since the Full backup was taken. To restore a differential backup you must also have the latest Full backup available.
o Backup Database (Transaction Log)
Databases that are in Full Recovery Model need a periodical backup of their transaction logs. You don’t need this option for databases that are in the Simple Recovery Model.
o Maintenance Clean Up Task
As the name implies, this option removes files left over from executing a maintenance plan.
Ø In what order do the steps of the Maintenance Plan take place?
You can configure the sequence order inside this menu:
Ø What are the system databases and the user databases?
System databases are the databases that are by default installed when you first install your SQL Server. They are vital to the operation of SQL Server:
o Master (critical database that needs to be backed up periodically)
o Model (this database is not modified by default, so usually no need to be backed up)
o MSDB (this is an important database to backup periodically)
o TEMPDB (you don’t need to backup this database at all)
I will periodically add more FAQs as I receive them :)
Disclaimer
This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.