The MS SQL Server 2005 SQL Server Management Studio application will be used to create and implement your database maintenance plans for full and incremental (transaction log) backups. Instructions for logging in to SQL Server 2005 with the Management Studio are provided below.

1. Open the SQL Server Management Studio for MS SQL Server 2005. By default, the SQL Server Management Studio is available in the Microsoft SQL Server 2005 program group in the Start:Programs menu.
   
2. The Connect to Server window will open.
   
3. Select “Database Engine” in the Server Type field.
4. Select the server on which MS SQL Server 2005 is installed in the Server name field.
5. Select “SQL Server Authentication” in the Authentication field.
6. Type the “sa” user (without quotes) and password in the Login and Password fields.
7. Click Connect when finished. The SQL Server Management Studio will connect and log in to the SQL Server specified.
   
   At this point you have connected to the MS SQL Server with the Management Studio. You can continue with implementing the database maintenance plans by following the procedures described in the rest of this chapter.

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.

At this point you have created a maintenance plan to create daily full backups, incremental (transaction log) backups every 2 hours, and to remove full backup files that are more than 2 weeks old. In addition to this, it is imperative that you modify the maintenance plan to remove incremental (transaction log) backups that are more than 2 weeks old.

Note: Failure to modify the maintenance plan to remove incremental (transaction log) backups will result in a situation where over time, your incremental (transaction log) backups will consume all available storage space.

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”.
2. Expand the Management branch, then expand the Maintenance Plans Branch. The database maintenance plan defined in the previous procedure will be listed.
   
3. Double-click the Maintenance Plan defined in the previous procedure. The maintenance plan will open in the display pane on the right side portion of the window.
   
   The display pane shows the Plan properties, including the subplan list and the plan designer. The subplan list displays all subplans that have been defined for the plan. Sublans include maintenance tasks defined in the previous procedure. When a subplan is selected in the list, tasks related to the plan appear in the Plan Designer in the bottom-right portion of the pane.
   
4. Select Subplan_3 in the subplan list. This corresponds to the maintenance cleanup tasks defined in the previous procedure. When selected, the tasks assigned to the plan will appear in the Plan Designer. In this case, since Subplan_3 is the maintenance cleanup task defined in the previous procedure, the maintenance cleanup task should be displayed in the designer pane.
   
5. Drag a maintenance cleanup task from Maintenance Plan Tasks list in the Toolbox to the Plan Designer pane.
   
6. Connect the arrow from the original maintenance cleanup task to the new maintenance cleanup task.
   
7. Double-click the newly added Maintenance Cleanup Task. The Maintenance Cleanup Task Properties window will open.
   
8. Select Local Server Connection in the Connection field.
9. In the Delete files of the following type section of the window, select the following option:
   • Backup files radio button.
10. In the File Location section of the window, select the following option:
    • Search folder and delete files based on an extension radio button.
    • Browse to and select the backup folder selected in the previous procedure for saving backup files.
    • Enable the Include first-level subfolders checkbox.
11. In the File age section select the following options:
    • Enable the Delete files based on the age of the file at task run time checkbox.
    • Delete files older than 2 Weeks.
      When finished, the Maintenance Cleanup Task Options should look like the following screen capture:
      
12. Click OK. The Maintenance Cleanup Task Properties window will close, returning you to the plan designer.
    
13. Click Save ( ). The modified maintenance plan will be saved.

At this point you have modified your database maintenance plan to automatically remove incremental (transaction log) database backups that are more than 2 weeks old. Please continue to “Post Implementation Steps”.

After you have created and modified the database maintenance plan, you should validate the plan by doing the following:

Validate the maintenance plan. Run each subplan and check that full backups, incremental backups, and backup reports are being created in the specified locations. You can manually run each subplan by expanding the SQL Server Agent:Jobs branch in the navigation pane and right clicking and selecting Start Job at Step... for each subplan included in the maintenance plan.

Validate the deletion of old backup files. If you set the maintenance plan to remove backup files that are more than 2 weeks old, make sure to check your backups in two weeks to ensure that old backup files are being removed. It may be helpful to set a reminder in your mail, calendar, or scheduling software so that you don’t forget to do this. If backups are not removed, it is probable that the old backup files will eventually consume all available storage space, resulting in a failure to create new backup files and even interfering with the ability of your database to grow.

Ensure that your database backup files are incorporated in your corporate tape backup plan. The maintenance plans created above merely create backups of your databases to disk, but this will not protect you if the disks on which these backups reside are destroyed, rendered unusable, etc....