Project Insight data is stored in the database configured for Project Insight during the installation process. Additionally, file uploads are contained in the Windows file system. A backup and restore strategy including both the database backup files and uploaded files will contain all of the data required in the event that a new Project Insight server install is required or a data recovery from the backup becomes necessary.
All data backup and restore procedures must be implemented within an existing corporate procedure by systems administration professionals with Microsoft SQL Server database administrator experience. If you do not have on-site personnel with expertise in data recovery and Microsoft SQL Server database administration, it is imperative that a local consultant is requisitioned to design, implement and maintain the data backup and recovery strategy for your organization.
The procedures in this document are intended to be used as an example only. Actual processes and configurations should only be recommended and implemented by qualified personnel routinely familiar with your corporate procedures and standards. Metafuse does not provide these services, but may be able to refer you to a Microsoft Certified Partner in your region for assistance with your disaster recovery strategy.
File Upload Backup
Uploaded files are stored based in the location specified during the Project Insight installation process. The default location for these files is in the ProjectInsight application folder under the drive:\Inetpub\Project Insight\Sites\Files folder. File uploads are stored within folders named the same as the Global Unique Identifier (GUID) assigned by the database at the time that the file upload is performed. Recovering these files within the proper folder structure allows the Project Insight application to locate them in accordance with the reference URLs stored in the database for use within the Project Insight user interface.
SQL Server Data Backup
SQL Server Management Studio must be used to create a schedule and location for the backup files to use in the event of a data recovery requirement. Scheduling in the SQL Server Management Studio to create these backup files prior to any backup routine which copies the files to another system or removable media is therefore imperative to ensure that the target backup media contains the latest Project Insight database data. The procedure used as an example in this document does not cover the procedure for installing or maintaining removable storage devices.
A database maintenance plan is created within the appropriate database server selection from SQL Server Management Studio in the “Management -> Maintenance Plans” selection. Right-click to create a “Maintenance Plan Wizard” as follows:
A maintenance plan setup wizard will guide you through the process of naming the maintenance plan and determining schedules for the tasks within the plan. If you choose separate schedules for each task as we have done in this example, you will either need to set a schedule for each task or run the maintenance plan on-demand (manually). Setting a single schedule would be the option to choose if you wanted all of the tasks within the plan to run at a specific interval without task exceptions, such as everyday at 2 AM all tasks will proceed as configured in the maintenance plan.
Select the maintenance tasks to be performed based on your needs. In this example, we have chosen to do full database backups along with some cleanup and index maintenance.
Select the order in which you would like the maintenance tasks to be performed.
Define the Check Integrity Task and select the Project Insight database.
Define Back Up Database (Full) Task and select the Project Insight database.
Define the Reorganize Index Task, select the Project Insight database and for Object select Tables and view.
Define Update Statistics Task by selecting the Project Insight database and Tables and views for Objects.
Define Maintenance Cleanup Task, if you wish to keep files up to 4 weeks it is the default, but a week should be sufficient.
Verify your settings and complete the maintenance plan.
Important note: The above example does not have a schedule for each of the tasks so they would only be performed on-demand. You will want to choose a schedule for each task or have the entire plan on a single schedule. The maintenance plan is not a substitute for data backup to removable storage and/or redundant systems. The maintenance plan simply makes a backup file which can be restored. The files created as part of the maintenance plan must be utilized by your existing backup procedures so that the data is available for disaster recovery.
Remember that SQL Server data must be created as recoverable data and then backed up. Missing this maintenance plan step and making copies of files in the database instead, either by copying or through the use of imaging software, will result in files which may not be recoverable at the crucial moment of need during a disaster recovery event.