Database backup and maintenance planning

ADMINISTRATOR

A database backup and maintenance plan creates a required task workflow to ensure your FactoryLogix Database is optimized, backed up regularly, and remains free of inconsistencies.

Warning

Be sure to safeguard your FactoryLogix data! Proper maintenance of your FactoryLogix Database is critical—without regular, scheduled database backups and maintenance, you run the risk of not having a recent backup of your FactoryLogix data if database problems occur.

If something happens to your FactoryLogix Database, you'll want to recover it as quickly as possible with minimum data loss. A solid database maintenance plan also assures that your transaction logs are properly maintained and don't consume all available disk space.

This topic provides an ideal maintenance plan for the FactoryLogix Database. There are many ways to set up a plan, but the goal is to have a simple plan that backs up your FactoryLogix Database during scheduled intervals while also backing up the database transaction logs.

Important

The details of each maintenance element will vary from factory to factory and you should adjust your maintenance plan based on system usage analysis, SQL server performance analysis, and your specific data recovery needs.

Note

For more information about SQL Server maintenance plans, see the Microsoft topic Use the Maintenance Plan Wizard.

Create a database maintenance plan

The SQL Server Maintenance Plan Wizard helps database administrators create a maintenance plan that the Microsoft SQL Server Agent will run on a regular basis to safeguard your FactoryLogix data. The wizard takes you through each task and its related settings. Although the procedure in this topic doesn't go into granular detail for each option, there is a Help button on each page of the wizard to assist you as you make choices for your maintenance plan.

Important

You can run a maintenance schedule in a variety of intervals. We recommend that you run the maintenance plan at least once a week to ensure that your data is backed up. Depending on resources, you can choose to run the plan daily, but keep in mind that the amount of storage required for database backups can be high.

Before you decide to run a maintenance plan daily, make sure your system has enough storage capacity.

Log onto the SQL Server with a Windows account that has local Administrator rights.
Open SQL Server Management Studio.
In the Object Explorer, expand the appropriate SQL Server computer/instance name, then expand the Management folder in the tree.
Right-click the Maintenance Plans folder, then select Maintenance Plan Wizard.
When the Maintenance Plan Wizard displays, select Next. In the Name field, enter FactoryLogix Maintenance Plan, then select Single schedule for the entire plan or no schedule.

Tip

As you move through the wizard pages, there’s a Help button to assist you.

Select the Change button to change the schedule for this maintenance plan.
In the New Job Schedule dialog, select Recurring next to Schedule Type, frequency, for your maintenance plan, select OK, then select Next.

Note

If you have a large database and your maintenance plan takes a long time to run, you may want to break up the schedule by the task that is being performed. This can be done after you create the plan.

Select the tasks you want to perform each time the maintenance plan runs, then select Next. (A description of each task displays when you select its corresponding check box.)

Note

Each customer’s selected maintenance tasks may differ from the following illustration.

The default maintenance task order is fine, so you don't need to change it.

Select Check Database Integrity, then select Next.
Select the FactoryLogix database you want to perform an integrity check on, select the check box for your database, select OK, then select Next.
Select the database to have the indexes reorganized, select the check box for your database, select OK, then select Next.
Select Tables and views from the Object drop-down to configure the Reorganize Index task, then select Next.
Select the database you would like to rebuild indexes, select the check box for your database, then select OK.
Select the desired settings for the History Cleanup task, then select Next.
Select the database you are backing up, select the check box for your database, then select OK.

Warning

The default path for database backup is the installation path for SQL Server. We recommend that you do not store the backup on the system drive or on the same drive where the database resides---if either of these drives fail, you will lose the backup!

Browse to the folder where you want to store backups, then select Next.
Select the database transaction log you are backing up, select the check box for your database, then select OK.
Define the maintenance cleanup task.
Define a location for your backup reports.
Review the settings for each maintenance task in the tree, then select Finish to complete the Maintenance Plan Wizard.
Verify that all steps for creating the maintenance plan were completed successfully.
Use the Report drop-down to view, save, or email the report.
When you're finished, select Close to complete the wizard.