Scheduling Full Database Backups by Using the Database Maintenance Plan Wizard

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

The easiest way to back up your MIIS database is to use the Database Maintenance Plan Wizard, which is available in SQL Server Enterprise Manager. By using the Database Maintenance Plan Wizard, you can create a job that runs full database backups and transaction log backups on a scheduled basis.

The procedures for backing up the MIIS database are the same as those for backing up SQL Server databases.

Important

It is recommended that you schedule transaction log backups to occur more frequently than the database backups (for example, every four hours). Backing up the transaction log truncates (removes) logged transactions and can improve performance.

To schedule backups by using the Database Maintenance Plan Wizard

  1. In Microsoft SQL Server, click Enterprise Manager.

  2. Expand the server group where the MicrosoftIdentityIntegrationServer database is located, and then expand the server.

  3. On the Tools menu, click Database Maintenance Planner.

  4. On the Start page, click Next.

  5. On the Select databases page, click These databases, click MicrosoftIdentityIntegrationServer, and then click Next.

  6. On the Update Data Optimization page, if you want the database to be shrunk automatically, freeing up unused space, do the following:

    Note

    For an optional method of shrinking the database, see “Using the Auto Shrink Option to Maintain a Minimum of 25% Unused Space” later in this section. It is recommended that you choose one of these methods to automatically (and regularly) shrink database size.

    • Select Remove unused space from database files, and then in the When it grows beyond box, keep the default value of 50 megabytes and in the Amount of free space to remain after shrink box, keep the default value of 10%,

    • Click Change, and then specify a schedule that shrinks the database, setting a time that is at least one hour before your scheduled full backups.

      Allowing one hour usually gives sufficient time for the database shrink to complete before the scheduled backup begins.

  7. Click Next until you reach the Specify the Database Backup Plan page.

  8. On the Specify the Database Backup Plan page, do the following:

    • Select the Back up the database as part of the maintenance plan and Verify the integrity of the backup when complete check boxes.

    • Under Location to store the backup file, click the tape or disk on which you want to store the backup, and then click Change.

    • In the Edit Recurring Job Schedule dialog box, specify the frequency, time, and duration of your backup job.

    • Make sure that the Enable schedule check box is selected, and then click OK.

  9. (Optional) If using the Full Recovery or Bulk-Logged Recovery model, do the following:

    • On the Specify the Transaction Log Backup Plan page, select Back up the transaction log as part of the maintenance plan, and Verify the integrity of the backup when complete.

    • Under Location to store the backup file, click the tape or disk on which you would like to store the backup, and then click Change.

    • In the Edit Recurring Job Schedule dialog box, specify the frequency, time, and duration of your backup job.

    • Make sure that the Enable schedule check box is selected, and then click OK.

  10. On the Specify Transaction Log Backup Disk Directory page, select a location for the transaction log backup, and then specify if you want older backup files automatically removed.

  11. Accept the default selections on the remaining wizard pages until you reach the Completing the Database Maintenance Plan Wizard page, and then in the Plan Name textbox, type a name for your backup plan.

Using the Auto Shrink Option to Maintain a Minimum of 25% Unused Space

In addition to shrinking the database automatically (see step 6 of the preceding procedure), you can also use the Auto Shrink option to automatically shrink the database each time the database file contains more than 25% unused space. It is recommended that you choose one of these methods to automatically shrink database size on a regular basis.

For more information about shrinking the database, see Microsoft SQL Server Books Online.

To enable Auto Shrink

  • In Databases, right click the MicrosoftIdentityIntegrationServer database, click Properties, click Options, and then in Settings, select Auto Shrink.