The first step in creating the maintenance plan is to use the Maintenance Plan Wizard to create an initial maintenance plan that will perform the following tasks:

• Create full database backups.
• Create incremental (transaction log) backups.
• Remove full backup files that are more than 2 weeks old.

Warning: The initial maintenance plan does not remove incremental (transaction log) backup files that are more than two weeks old. To accommodate this, the maintenance plan will have to be modified after it is created. See “Modifying the Maintenance Plan to Remove Old Transaction Log Backups” for step by step instructions.

1. Open the SQL Server Management Studio for MS SQL Server 2005. If it is not already open, refer to “Connecting to SQL Server with the Management Studio” above.
2. Expand the “Management” branch in the navigation pane and right-click the Maintenance Plans option. A context menu will appear.
   
3. Select Maintenance Plan Wizard in the context menu. The Maintenance Plan Wizard Welcome window will open.
   
4. Click Next. The Select Plan Properties window will open.
   
5. Type a name for the maintenance plan in the Name field. The choice of name is up to you, but it should be unique and descriptive of the task(s) it will perform. For example: “MC_DB_Maintenance”.
6. Type a description for the maintenance plan in the Description field.
7. Select the “Separate schedules for each task” radio button. This is required so that each task in the maintenance plan (create full backups daily, create incremental (transaction log) backups every 2 hours, and remove old full backup files) can operate on their own independent schedule.
8. Click Next. The Select Maintenance Tasks window will open.
   
9. Select the following Maintenance Tasks in the list:
   • Back Up Database (Full) - will be defined to create full database backups.
   • Back Up Database (Transaction Log) - will be defined to create incremental (transaction log) database backups.
   • Maintenance Cleanup Task - will be defined to remove full backup files that are more than 2 weeks old.
10. Click Next. The Select Maintenance Task Order window will open.
    
11. Click Next. The Define Back Up Database (Full) Task window will open.
    
12. Click the Database(s) field at the top of the window. A Database Selection window will appear.
    
13. Select the All user databases radio button. This will ensure that your current MetaCommunications database is included in the maintenance plan for full backups. If you create additional databases in the future (perhaps for testing), they will be automatically included in the maintenance plan.
14. Click OK. The Database Selection window will close, returning you to the Define Back Up Database (Full) Tasks window.
15. Set the following options in the remaining fields of the of the Define Back Up Database (Full) Tasks window:
    • Backup component - Select the “Database” radio button. If it is grayed out but selected, continue to the next option.
    • Destination - Select the “Backup to Disk” radio button.
    • Destination - Select the “Create backup file for every database” radio button. This will ensure that each database will be backed up to its own file (as opposed to one file containing backups for all databases).
    • Destination - Enable the “Create a sub-directory for each database” checkbox. This will create backups for each database in a unique sub-directory based on the database name within the specified backup folder.
    • Destination - Select a location where the database backups will be saved. You can accept the default location, or browse to and select a custom location. Regardless of the location you select, make sure to make note of the location, as you will need this information later when modifying the maintenance plan to remove old copies of backup files.
    • Destination - Type “bak” in the “Backup file extension” field. When full database backups are created, they will appear as .bak files in subfolders within the selected backup location folder.
    • Optional: Destination - Enable the “Verify backup integrity” checkbox if you want the full backup maintenance plan to check that the backup file is complete and readable after each backup file is created.
      After setting the Database Back Up Task options, the window should look similar to the screen capture below:
      
16. Click Change... in the Schedule section. This will open the Job Schedule Properties window for the Back Up Database (Full) task, allowing you to define a schedule for creating full database backups.
    
17. Accept the default or type the name of the Job Schedule in the Name field. The choice of names is up to you, but it should be unique and something recognizable or meaningful to you.
18. Select “Recurring” in the Schedule type field if it is not already selected.
19. Enable the “Enabled” checkbox.
20. In the Frequency section, select the following options:
    • Occurs - Daily.
    • Recurs Every - 1 day(s).
21. In the Daily frequency section, select the following option:
    • Occurs once at: 1:00AM (Select a time where database use will be at its minimum).
22. In the Duration section, select the following options:
    • Start Date - Enter today’s date if it is not already entered.
    • Select the “No End Date” radio button.
      When finished specifying options in the Job Schedule Properties window, it will look similar to the screen capture below:
      
23. Click OK. The Job Schedule Properties window will close, returning you to the Define Back Up Database (Full) Task window.
24. Click Next. The Define Back Up Database (Transaction Log) Task window will open, allowing you to set up the maintenance plan to create incremental (transaction log) backups of all user databases.
    
25. Click the Database(s) field at the top of the window. A Database Selection window will appear.
    
26. Select the All user databases radio button. This will ensure that your current MetaCommunications database is included in the maintenance plan for full backups. If you create additional databases in the future, they will be automatically included in the maintenance plan.

    Note: Be sure that the database recovery model for all user databases is set to “Full”. If any user databases have a recovery model set to “Simple”or “Bulk Logged”, they will not have incremental (transaction log) backups created for them. The recovery model for each database can be reviewed and (if needed) changed in the Options group of each database’s Properties window.

27. Click OK. The Database Selection window will close, returning you to the Define Back Up Database (Transaction Log) Tasks window.
28. Set the following options in the remaining fields of the of the Define Back Up Database (Transaction Log) Tasks window:
    • Backup component - Select the “Database” radio button. If it is grayed out but selected, continue to the next option.
    • Destination - Select the “Backup to Disk” radio button.
    • Destination - Select the “Create backup file for every database” radio button. This will ensure that each database will be backed up to its own set of transaction log files (as opposed to one set of transaction log files being used for all database backups.
    • Destination - Enable the “Create a sub-directory for each database” checkbox. This will create backups for each database in a unique sub-directory based on the database name within the specified backup folder.
    • Destination - Select a location where the database backups will be saved. You can accept the default location, or browse to and select a custom location. Regardless of the location you select, make sure to make note of the location, as you will need this information later when modifying the maintenance plan to remove old copies of backup files.
    • Destination - Type “trn” in the “Backup file extension” field. When transaction log database backups are created, they will appear as .trn files in subfolders within the selected backup location folder.
    • Optional: Destination - Enable the “Verify backup integrity” checkbox if you want the incremental (transaction log) backup maintenance plan to check that the backup files are complete and readable after backup files are created.
      After setting the Back Up Database (Transaction Log) Task options, the window should look similar to the screen capture below:
      
29. Click Change... in the Schedule section. This will open the Job Schedule Properties window for the incremental (transaction log) database backup task, allowing you to define a schedule for creating incremental (transaction log) backups.
    
30. Accept the default or type the name of the Job Schedule in the Name field. The choice of names is up to you, but it should be unique and something recognizable or meaningful to you.
31. Select “Recurring” in the Schedule type field if it is not already selected.
32. Enable the “Enabled” checkbox.
33. In the Frequency section, select the following options:
    • Occurs - Daily.
    • Recurs Every - 1 day(s).
34. In the Daily frequency section, select the following option:
    • Occurs every: 2 hours. You can vary this increment, but be aware of the following: The shorter the increment, the more frequent the transaction log backup files are created, reducing the amount of potential data loss in the event that you need to restore the database. However, this will increase the amount of storage space consumed. You can increase the time between transaction log backups to save storage space, but this will increase your exposure to data loss in the event that you need to restore from backups.
35. In the Duration section, select the following options:
    • Start Date - Enter today’s date if it is not already entered.
    • Select the “No End Date” radio button.
      When finished specifying options in the Job Schedule Properties window, it will look similar to the screen capture below:
      
36. Click OK. The Job Schedule Properties window will close, returning you to the Define Back Up Database (Transaction Log) Task window.
37. Click Next. The Define Maintenance Cleanup Task window will open. This task will be used for removing old full database backup files only.

    Note: The removal of old incremental (transaction log) database backup files is accomplished by modifying the maintenance plan after it has been created. Instructions for this are provided in “Modifying the Maintenance Plan to Remove Old Transaction Log Backups.”

    

38. In the Delete Files of the following type section, select the following option:
    • Enable the Backup files radio button.
39. In the File location section, select the following options:
    • Enable the “Search for folder and delete files based on an extension” radio button.
    • Browse to and select the location of the database backup folder in the Folder field. This is the location in which the maintenance plan will look for old backup files. This location must be identical to that defined in Step 15.

      Note: If there is an inconsistency between the location used to save full backup files (as defined in Step 15) and the location specified to look for old backup files (in the above step), backup files will still be created, but they will never be removed (even if they are more than 2 weeks old). This can result in a situation where you consume all available hard drive space with backup files.

    • Type “bak” in the File extension field.
    • Enable the “Include first-level subfolders” checkbox.
40. In the File age section, select the following options:
    • Enable the “Delete files base on the age of the file at runtime” checkbox.
    • Specify the option to delete files more than 2 weeks old. This is the recommended setting. You may wish to increase or decrease this interval depending on your needs and requirements. Increasing the frequency will result in more storage space used for full backups, but will reduce the amount of information loss in the event that you have to restore from a backup. Decreasing the frequency will result in less storage space used for full backups, but will increase the amount of information loss in the event that you have to restore from a backup.
      When finished defining your Maintenance Cleanup Task options, the window will look similar to the following screen capture:
      
41. Click Change... in the Schedule section. This will open the Job Schedule Properties window for the maintenance task, allowing you to define a schedule for deleting old database backup files.
    
42. Accept the default or type the name of the Job Schedule in the Name field. The choice of names is up to you, but it should be unique and something recognizable or meaningful to you.
43. Select “Recurring” in the Schedule type field if it is not already selected.
44. Enable the “Enabled” checkbox.
45. In the Frequency section, select the following options:
    • Select the Occurs once at radio button and type or select a time of day at which you would like old backup files to be removed.
46. In the Duration section, select the following options:
    • Type or select today’s date in the Start date field.
    • Select the “No End Date” radio button.
      When finished specifying options in the Job Schedule Properties window, it will look similar to the screen capture below:
      
47. Click OK. The Job Schedule Properties window will close, returning you to the Define Maintenance Cleanup Task window.
48. Click Next. The Select Report Options window will open.
    
49. Browse to a destination in which you want backup report files to be saved. If you chose a custom destination for your backup files, you may wish to create a \Log folder in the backup directory and select this location as the location in which you want backup report files to be saved.

    Note: It is important to review backup reports to ensure that your database backups are being created successfully.

50. Click Next. The Complete the Wizard window will open.
    
51. Click Finish. The Maintenance Plan Wizard Progress window will appear as the maintenance plan is saved.
    
52. Review the status of the Wizard. All entries should read “Success”.
53. Click Close when the full backup maintenance plan is saved.
    At this point you have created a maintenance plan to perform full database backups every day, incremental (transaction log) backups every 2 hours, and a maintenance cleanup task that removes full database backups that are more than 2 weeks old. The only remaining task is for you to modify the cleanup task to also remove incremental backup files that are more than 2 weeks old. See “Modifying the Maintenance Plan to Remove Old Transaction Log Backups” for more information.