Skip to content

I want to backup Pleasanter's DB data regularly (SQL Server)

Answer

Set up regular "Backup" in SQL Server Integration Service Manager (SSIS).
If you are using SQL Server Express, please refer to Back Up The Database (SQL Server) in Pleasanter.

Overview

If you are using SQL Server for the database, you can set up regular backups of the database in SQL Server Integration Service Manager (SSIS).
If you are using SQL Server Express, you cannot use SSIS, so please refer to Back Up The Database (SQL Server) in Pleasanter.

Operation Procedure

1. Install SQL Server Integration Service Manager (SSIS)

*If it is already installed, skip this step.

  1. Start the installer from the SQL Server 2017 media and select "Install" - "Perform a new standalone installation of SQL Server or add features to an existing installation".
    SQL Server installer Installation page, where a new standalone installation can be started
  2. Follow the SQL Server setup wizard to proceed with the installation.
  3. In [Installation type], select "Add features to an existing SQL Server 2017 instance (A)", select the target instance, and then select "Next (N)".
    Setup wizard Installation Type page with the option to add features to an existing instance
  4. In [Feature selection], check "Integration Services" from [Features (F)], and select "Next (N)".
    Setup wizard Feature Selection page with Integration Services checked
  5. Follow the SQL Server setup wizard to proceed with the installation.
  6. After the installation is complete, start [SQL Server 2022 Configuration Manager] and start the SSIS service.
    SQL Server Configuration Manager with the SSIS service started
  7. If the SQL Server Agent is not running, right-click it, select "Properties", and click "Start" from the "Log On" tab of the properties dialog. Also, change "Start Mode" to "Automatic" from the "Service" tab.

2. Setting The Maintenance Plan

  1. From SQL Server Management Studio, expand the "Manage" folder from the target SQL Server, right-click the "Maintenance Plan" folder, and select "Maintenance Plan Wizard (W)".
    Right-click menu on the Maintenance Plan folder in SQL Server Management Studio

    *For details on the Maintenance Wizard, please refer to the Microsoft documentation below.
    https://docs.microsoft.com/ja-jp/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-2017

  2. Follow the Maintenance Plan Wizard to proceed with the settings.

  3. In "Select Plan Properties", set the following:

    • Name: Set the name of the maintenance plan as desired
    • Select "Use a single schedule for the entire plan or no schedule"
    • Schedule: Select "Change"

    Select Plan Properties page of the Maintenance Plan Wizard

  4. In "New Job Schedule", set the following and select "OK".

    • Frequency: Set the desired frequency (Set the frequency at which the job you are configuring will be executed)
    • Daily frequency: Set whether to execute once when the frequency occurs or to continue to execute at regular intervals
    • Execution time: Set the desired execution time (Set the date to start the job schedule you are configuring)

    New Job Schedule page where frequency and execution time are set

  5. Check the schedule and select "Next (N)".
    Wizard page showing the schedule that has been configured

  6. In "Select Maintenance Tasks", check "Database backup (full)" and "Maintenance cleanup task", then click "Next (N)".
    Select Maintenance Tasks page with database backup and maintenance cleanup checked

  7. In "Select Maintenance Task Order", set the order to "Database Backup (Full)" -> "Maintenance Cleanup Task" and select "Next (N)".
    Select Maintenance Task Order page listing the backup task before the cleanup task

  8. In "Define Database Backup (Full) Task", set the following on the "General" tab.

    • Database: Set the database to be backed up (the database used by Pleasanter)
    • Backup components: Select "Database (E)" to back up the entire database

    General tab of the Define Database Backup (Full) Task page

  9. On the "Backup Destination" tab, set the following and select "Next (N)".

    • Create backup files for all databases: Specify the folder to create the backup files in "Folder (L)"
    • Backup file extension (O): Specify "Bak"

    Backup Destination tab where the output folder and file extension are specified

  10. On "Define Maintenance Cleanup Task", set the following and select "Next (N)".

    • Delete the following types of files: Select "Backup Files (K)"
    • File location: Select "Search Folders And Delete Files Based On File Extension (O)"
      • In "Folder (D):", specify the folder to delete backup files from
      • In "File Extension (I):", specify "Bak"
    • File age: Check "Delete Files Based On File Age When Task Is Executed (T)"
      • In "Delete Files Older Than (G):", specify "8 Days" (same deletion schedule as DbBackup.vbs in the Pleasanter manual)

    Define Maintenance Cleanup Task page where the file type and file age are specified

  11. Follow the Maintenance Plan Wizard to complete the maintenance plan set up.