The procedures described herein should help preserve data integrity. If you experience what appears to be data corruption and you cannot resolve the problem yourself, please see Troubleshooting before contacting us.

Great Plains Maintenance Procedures

Microsoft SQL Maintenance Procedures

Data Recovery

Great Plains Maintenance Procedures

The Great Plains system is designed to ensure maximum accuracy and integrity of your accounting data. Occasionally, however, your data tables may become damaged. Hardware failures, power surges, and other problems can damage or destroy data.

While damage occurs infrequently, the factors that cause it are difficult to predict or control, and it’s necessary to take measures to protect your data. Regularly back up your accounting data and perform table maintenance to minimize risk of data loss from table damage.

Only SA or DYNSA can open the Backup Company window to make backups. The backup procedure must be run on the server.

Backups Overview

A backup is a copy of your data tables on CDs, magnetic tape, or another medium. You can prevent loss of your company’s data by making frequent, regular backups. Having a good set of backups is like having insurance— without it you risk losing your information and spending a great deal of time reentering it.

In addition to making backups of your tables, you should back up your transaction-related information by printing and storing posting journals and reports, or by sending them to a file. Then, if you need to restore a backup, finding and reentering the information that’s been entered since the backup will be much simpler and quicker. Also, keep all of the reports that you usually use either as printed copies or in files. Detailed reports from open tables, tables containing current posted transactions, and history tables contain the most complete information.

To ensure that you always have current backups, you should design and follow a formal backup schedule. Be sure to incorporate a rotation plan so that you aren’t copying over the same CD or tape every day. This will eliminate the loss of data if damage isn’t detected for several days. Backups should be clearly labeled so that you can distinguish one set from another.

We also recommend that you label daily, weekly, and monthly backups separately so that they don’t become mixed together.

Database Backup Procedures

You should back up databases and transaction logs frequently, and you should save the backups.

The frequency and type of backups you do depends on two factors: the acceptable amount of work that can be lost due to media or other failure, and the volume of transactions that occur on the SQL server. For systems that have little update activity and that are used primarily for viewing data, weekly database backups might be sufficient. For high-volume environments, database backups may be needed daily and transaction logs hourly. The strategy chosen should fit your environment and provide adequate assurance of recovering needed data. The following is an example of a typical backup schedule:

ItemFrequencyMinimum Time to Keep Backup

Transaction log Twice a Day Two Weeks
Database and Transaction Log Every Day Two Months

 

Items to Back Up

  • Forms.dic –  If your Great Plains windows have been customized using the Modifier, back up the Forms.dic when you install it, or monthly if you use the Modifier to make additional customizations.
  • Reports.dic –  If you use Report Writer to modify or create reports, back up the Reports.dic file monthly as part of your system backups, or more frequently as changes are made.
  • Great Plains database – Back up all tables in the database monthly as part of your system backup, or more frequently as changes are made.
  • Each of your company databases – Back up each company database daily. In addition, the documentation for other procedures, such as table maintenance, may prompt you to make a backup as well.
  • msdb database – This is the database used by SQL Server Agent to store tasks. If you use SQL Server Agent to schedule automatic tasks, back up this database as part of your system backups.

If database backups are performed online, they should be scheduled for times when the server is not being heavily updated, because the backups will slow the server somewhat. In addition, the backups should be performed on a fixed schedule. By using a fixed schedule, users will always know when the backup is occurring and can expect a slight delay in performance, or they can plan to do other non- server-related tasks during that time.

When to Perform a Database Backup

It is important to back up a database either before or after the following procedures:

Creating a Database

Each database should be backed up just after it is created, and on a fixed schedule thereafter. For example, if you create a database on Monday and wait until Friday afternoon to back it up, you risk losing a whole week’s work if there is a media failure on Friday morning.

Performing An Operation That Isn’t Logged

You must back up a database any time you perform an operation that is not logged. If you don’t, the transaction log backup isn’t useful.

Database Maintenance Procedures

We recommend that you back up any affected tables before and after performing any database maintenance procedure that could possibly change your data. This includes Database Console Commands (DBCC) as well as the Update Statistics and Recompile functions within Great Plains. Power fluctuation or hardware failure can cause detrimental damage to your data when performing these tasks.

Data Recovery Procedures

Back up all affected tables before and after performing data recovery procedures in case of power fluctuations or hardware failure during these processes. Back up data before restoring a backup in case you need to refer to it later or in case your current backup is damaged.

Updating or Installing Additional Products

Back up your entire Great Plains system before and after updating to a new version of Great Plains or installing additional products. Power fluctuations and hardware failure can cause detrimental damage during an update. If your data is damaged before you update your system, you’ll need to restore the backup to fix any damage.

Backup Procedure

To back up data:
1. Open the Backup Company window. (File >> Backup)
2. Select the company you want to back up, or System Database to back up system data.
3. The path and file name of the backup file are displayed. You can modify the path and file name as needed.
4. Click OK to make the backup. The window will be closed and a message will appear when the backup is complete.

Microsoft SQL Maintenance Procedures

Database maintenance helps keep the inner workings of Microsoft SQL Server and your Great Plains databases running at their peak level. We recommend that you use the following SQL Server maintenance procedures:

  • Database consistency check commands
  • Update statistics
  • Recompile stored procedures

We recommend that you perform these maintenance procedures weekly for the Great Plains database and all company databases. However, you can vary this frequency based on your environment and the amount of activity each database receives. We also recommend that you stagger these maintenance procedures throughout the week to handle large volumes of data. Only SA or DYNSA can run SQL maintenance.

Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

Database Consistency Check Commands

You can run database consistency check (DBCC) commands manually with SQL Server through SQL Query Analyzer. To do this, follow these steps:

1.   Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

2.   In SQL Query Analyzer, run one or more of the following DBCC commands:

DBCC CHECKCATALOG (DBNAME)

When you use this statement, replace DBNAME with the name of your database, such as DYNAMICS.

DBCC CHECKDB (DBNAME)

When you use this statement, replace DBNAME with the name of your database, such as DYNAMICS. You can schedule this command within SQL Server Enterprise Manager so that the maintenance process runs automatically. To do this, on the Tools menu, click Database Maintenance Planner, and then click to select Check database integrity.

DBCC DBREINDEX (TableName)

When you use this statement, replace TableName with the name of the table, such as GL00100. The DBCC DBREINDEX command can also be executed manually against all tables at the same time using the Reindex.sql script in the Great Plains\SQL\Util folder. You can also schedule this command within SQL Server Enterprise Manager so that the maintenance process runs automatically. To do this, on the Tools menu, click Database Maintenance Planner, and then click to select Reorganize data and index pages.

Updating Statistics

There are several ways for you to run the update statistics procedure.

  • You can update statistics by running the UpdatSta.sql script with Microsoft SQL Server using SQL Query Analyzer. You can find the UpdatSta.sql script in the Great Plains\SQL\Util folder on the server.
  • You can run the update statistics process within Great Plains. To do this, follow these steps:

1. On the File menu, point to Maintenance, and then click SQL.

2. Select a database from the Database box.

3. Select all the tables for that database by pressing CTRL+A.

4. Click Update Statistics, and then click Process.

  • You can run the update statistics process automatically within SQL Server Enterprise Manager. To do this, right-click your database, click Properties, click Options, and then click to select Auto Create Statistics and Auto Update Statistics.
  • You can schedule the update statistics process on a recurring basis within Database Maintenance Planner. To do this, on the Tools menu, click Database Maintenance Planner, and then click to select Update statistics used by query optimizer.

Recompiling Stored Procedures

There are several ways for you to recompile stored procedures.

  • You can recompile stored procedures by running the Recomp.sql script with Microsoft SQL Server using SQL Query Analyzer. You can find the Recomp.sql script in the Great Plains\SQL\Util folder.
  • You can also recompile stored procedures within Great Plains. To do this, follow these steps:

1. On the File menu, point to Maintenance, and then click SQL.
2. Select a database from the Database box.
3. Select all the tables for that database by pressing CTRL+A.
4. Click Recompile, and then click Process.

  • You can recompile stored procedures by scheduling the sp_recompile procedure using SQL Server Agent Jobs. For additional information about steps to create a job within SQL Server Enterprise Manager, see Microsoft SQL Server Books Online.

Contact Computer Resources at 901-382-1634 if you receive any consistency or allocation errors when you are running these SQL maintenance procedures.

Database Maintenance Procedure Definitions

The following is a list of definitions of database maintenance procedures. For additional information, see Microsoft SQL Books Online.

  • DBCC CHECKCATALOG – Examines the consistency in system tables and between system tables. This command checks data pages and tables in the specified database for errors.
  • DBCC CHECKDB – Examines the allocation and structural integrity of all the objects in the specified database.
  • DBCC DBREINDEX – Rebuilds all indexes. This command will help reduce page splitting and will improve data modification performance.
  • Update statistics – Updates the query optimizer with distribution information of key values of indexes. This enables the query optimizer to make efficient decisions.
  • Recompile stored procedures – Recompiles the queries and optimizes the triggers used by stored procedures. This recompile procedure updates database statistics and optimization information that can become outdated with changes to the database.

Signs That Data Is Damaged

The most common indicator of data damage is an alert message that indicates an error in a specific table. However, data damage may not always be this obvious and it may be more difficult to identify in which table or tables it has occurred. Other indicators of data damage include:

  • Alert messages that you can’t explain
  • Inaccurate data in windows or on reports
  • Unusual characters in windows or on reports
  • Windows you’re unable to open

Troubleshooting

Once you’ve determined that a problem exists, you can use the following questions to direct your troubleshooting effort.

Does the error occur for a system administrator user?
If a system administrator user does not receive the same error as other users, a permission problem may exist. A script called Grant70.sql is included on the Great Plains CD and is installed automatically during the Great Plain client/server installation process. Run this script against the database that produces the error. For more information on running scripts, see the SQL Server documentation.

Does the error occur for all users?
If the error does not occur for all users, a security problem may exist. You should also check customizations, such as modified reports and forms.

Remember that you have security options within SQL Server and MSDE, and within Great Plains.

Does the error occur in all companies?
If the error occurs in more than one company and is data-related, the problem is likely in the system database. The problem could also exist in one of your dictionaries, such as Dynamics.dic or Reports.dic. If an error occurs in only one company, the problem likely exists in the company database.

Does the error occur on all workstations?
To determine if a problem is data-related or dictionary-related, verify whether the error happens on all workstations. If all workstations produce the same error, the problem is likely on the server rather than on the individual client. The problem could be related to database tables or to shared files. To determine where damage has occurred, see Finding Which Tables Contain Damaged Data, below.

Does the error happen consistently?
If an error occurs consistently, you probably have damaged data. To determine if a table is corrupt, try isolating records within the tables you’re working with. For more information on determining where damage has occurred, see Finding Which Tables Contain Damaged Data, below.

Is the window or report modified?
If the non-modified version of a window or report does not receive the error, the problem is related to the modifications to the dictionary. If recent modifications have been done, check the modifications for errors. If the modifications have worked in the past, rename the dictionary and restore it from a backup.

Do not delete dictionaries. Renaming files allows you to restore these files later, if necessary.

Are there integrating products or customizations?
If there are customizations or integrating applications, try removing them to see if errors still occur. If integrating applications are present, remove the dictionaries from the Dynamics.set file and rename the associated file extensions in Windows Explorer.

Do not delete these files. Renaming file extensions allows you to restore these files later, if necessary.

If customizations exist on your system, contact the person who made the customizations for troubleshooting assistance.

If you’re having printing problems, are you able to print to the screen, a file, or another printer? When you notice a problem on a report or inquiry window, verify whether the error occurs when you view the information using a different medium, such as printed to the screen or to a different printer. If a report and its associated inquiry window produce the same results, the problem is likely damaged data. If only the report is incorrect, the problem could be related to a modified report.

Were the transactions imported or keyed?
If imported transactions produce errors, verify whether manually keyed transactions produce the same results. Because some methods of importing data do not require the data to be verified for accuracy, imported data may be corrupt or incomplete. To determine where damage has occurred, see Finding Which Tables Contain Damaged Data, below.

Does the problem exist if processing is done at the database server?
If the problem does not exist when all processing is performed at the server, the problem may be related to your network, ODBC drivers, or ODBC data sources or a result of differing MDAC versions.

Finding Which Tables Contain Damaged Data

Once you’ve established that table damage has occurred in Great Plains, the next step is to find out which table or tables are affected. Once you’ve determined which tables need to be repaired, see the Recovering Damaged Data checklist in Data Recovery, below.

  • If an alert message has appeared stating the name of the table, you can begin the data recovery checklist immediately.
  • If unusual results on a report indicate a damaged table, refer to the sample reports provided with the module to see which table groups’ data is printed in that report.
  • If you’re having trouble opening a window, use the Window Descriptions window (Tools >> Resource Descriptions >> Windows) to determine the physical and table groups accessed by a window.
  • If you still can’t determine which table is causing the problem, try to isolate the problem. For example, if you’re working in Sales Order Processing, try entering different types of transactions with various items for various customers. If the error occurs only for a specific customer record, you can conclude that the data in the RM Customer MSTR table is corrupt.

Each Great Plains table has three names: a display name, a technical name, and a physical name. Display names are displayed in the Check Links window and other windows. The table names that appear in alert messages are typically technical names, the names that the system uses to identify tables. For example, a message may state that an error occurred in the GL_Account_MSTR table, but the display name for that table is Account Master.

You may need to use the Table Descriptions window (Tools >> Resource Descriptions >> Tables) to determine the table group to which a table in an alert message belongs. Some data recovery procedures can be performed only on table groups, while others can be performed on table groups or tables.

Alert Message Troubleshooting

If you receive an error message that indicates a problem you can’t explain, use the following resources for more information. If you are unable to resolve the problem yourself, contact Computer Resources.

Great Plains Messages

The best source of information for troubleshooting Great Plains alert messages is the TechKnowledge database on CustomerSource. Go to the Technical Q&A page, where you can type in the message number or message text to search for the alert message you’re receiving.

DBMS Messages

Microsoft SQL-related error messages appear as DBMS errors in Great Plains. Always use the SQL Server Books Online to troubleshoot DBMS errors (Start >> Programs >> Microsoft SQL Server >> Books Online). Select the Search tab and enter the error number, then choose List Topics. Either highlight and select Display or double-click an entry to open the topic. In the description column of the error message table, you’ll see more information about the error. You can also use the SQL Query Analyzer to find the same information.

Data Recovery

To recover damaged data, you must first determine the table or tables where the damage occurred, then determine the appropriate procedures to complete. For more information on determining the location of damage, see Finding Which Tables Contain Damaged Data, above.

It’s very important that the data recovery functions be performed carefully by an authorized user. Refer to your System Setup documentation (Help >> Contents >> select Setting Up the System) for information about setting up classes and security to determine users with access to these functions.

The data recovery information contains the following sections:

  • Recovering damaged data
  • Checking links
  • Reconciling tables
  • Restoring backups
  • Restoring data

Recovering Damaged Data

When you’ve determined the table or tables that are causing the problem, follow the steps in this checklist. If it’s possible that one or more tables are damaged, but you can’t determine which, perform the data recovery procedures on all tables that may be affected.

If you have a current backup that you made before your table damage occurred, you could restore it instead of completing the procedures in the recovering damaged data checklist. The more recent your backup is, the fewer transactions you’ll need to reenter.

To recover damaged data:

1. Make a backup.Always be sure you have a current backup of your company’s data before performing any table maintenance or utility procedures. These procedures deal directly with the data, and if there is an interruption during processing you will need to restore the current backup.

2. Update statistics and recompile stored procedures. Updating statistics reconfigures table keys and results in better performance; recompiling stored procedures adapts stored procedures to tables with significant increases or decreases in data. For more information, see Updating Statisticsand Recompiling Stored Procedures, above.

3. Check links. If you rebuild a table and the report shows that some records were removed, check links for the table. Checking links examines the table, checking corresponding information in related tables and, if possible, changing the damaged data to match the corresponding data in an undamaged table. For more information, see Checking Links, below. If the damaged table is in the System or Company series, do not check links. Instead, continue to the next step: Reconcile data.

4. Reconcile data. During a reconcile, Great Plains examines the data within different tables and checks to see whether information that is kept in two different tables has the same value in both.

5. Restore a backup. If reconciling is unsuccessful, restore your most recent backup. The more recent your backup, the fewer transactions you’ll have to reenter, and if you’ve printed or saved all of your posting journals, reentering the transactions can be a fairly simple process.

6. Clear data. Clearing data is the last and most drastic step. Before attempting to clear data, please call Microsoft Business Solutions or Computer Resources for assistance.

Checking Links

Checking links examines tables, checking corresponding information in related tables and, if possible, changing the damaged data to match the corresponding data in an undamaged table. If you were alerted to damage by an alert message indicating damage to a specific table, the name of the table won’t be listed in the Check Links window. Any user can run check links.

To check links:

1. Be sure that no one is using Great Plains. To view which users are in the Great Plains system and where, choose Tools >> Setup >> System >> User Activity.

2. Make a backup. Always make a backup before checking links.

3. Open the Check Links window. (File >> Maintenance >> Check Links)

4. Select the series containing the tables to check. If you know the name of the damaged table, but not the table group to which it belongs, refer to the Table Descriptions window (Tools >> Resource Descriptions >> Tables).

5. Select the tables to check links for, and choose Insert. To remove any table from the Selected Tables list, highlight the table name and choose Remove.

6. To insert tables from another series, repeat steps 4 and 5.

7. Choose OK to check links for the selected tables and print the Check Links Report. Checking links is performed as a background process, which means you can perform other tasks while the checking is being done.

  • Great Plains checks links in the selected tables.
  • The Report Destination window will appear, and you can specify where the Check Links Report should be printed. If you mark File, select the appropriate table format and enter the report file name.
  • The Check Links Report will display any information that was recreated.

We recommend that you send the Check Links Report to the screen, and then print it if necessary, because it may be very large. Each report can only be printed once each time you check links, so it’s a good idea to send the report to a file as well.

8. To determine what information to reenter, use the Table Descriptions window (Tools >> Resource Descriptions >> Tables) to view information for the table you checked links for, then use a window that accesses the table to reenter information. Some records are created through processes such as posting or aging, and this information can’t be reentered manually in a window.

You may want to create a report using Report Writer that lists all fields included in the table that you checked links for. This report can serve as a valuable reference tool. For more information, refer to the Report Writer manual.

9. If checking links is unsuccessful and the problems continue to occur, go to Reconciling Tables, below.

Reconciling Tables

You should reconcile your data if checking links doesn’t resolve the problem. Reconciling compares corresponding data in different table groups and removes any lone, or “orphan,” records. For example, a report option that was created for a report that no longer exists is an orphan record, and would be removed. Reconciling also checks to be sure that corresponding or identical information stored in two different tables is the same, and if there are discrepancies, changes the information in the table you’re reconciling to match the information in the table it’s being compared with.

For example, the number of periods in your fiscal year is stored in the Fiscal Periods Table and the Company Master Table. If you reconcile the Fiscal Periods Table and the number of periods is different than in the Company Master Table, the number of periods in the Fiscal Periods Table will be changed to match the number in the Company Master Table. Refer to the Table Descriptions window (Tools >> Resource Descriptions >> Tables) for more information on table groups and tables. Some tables can’t be reconciled. If you can’t reconcile the damaged table, restore a backup. Any user can reconcile tables.

To reconcile tables:

1. Be sure that no one is using Great Plains. To view who is in the Great Plains system and where, choose Tools >> Setup >> System >> User Activity.

2. Make a backup. It’s very important that you back up your tables before reconciling or performing any other table maintenance procedure.

3. Open the Reconcile window. (Tools >> Utilities >> System >> Reconcile)

4. Highlight each table to be reconciled and choose Insert. Use the All button to select all of the tables in the List to Reconcile or use the Remove button to remove any table from the list.

5. Choose Reconcile to reconcile the selected tables and print the Reconcile Report. The Report Destination window appears; specify where the reconcile report should be printed. If you mark File, select the appropriate file format and enter a report table location.

Always send the Reconcile Report to the printer, since it can be printed only once. It’s a good idea to send the report to a file, as well, in case of a printer malfunction.

The reconcile report will display any information that was changed, and list the number of records removed, if any. Use the information on the reconcile report to determine what information to reenter. Use the Table Descriptions window (Tools >> Resource Descriptions >> Tables) to view information for a table, then use a window that accesses that table to reenter information. Some records are created through processes such as posting or aging, and this information can’t be reentered manually in a window.

6. If the original problem continues to occur, restore backups. For more information, see Restoring Backupsor Restoring Data, below.

Restoring Backups

Always restore the entire database containing the affected table or tables. The information in your Great Plains system is so interrelated that it’s necessary to restore the database; we recommend that you restore a complete backup of your tables, if possible.

To restore backups:

1. Back up your current data.

Always make a backup of current data before restoring an earlier backup, in case you need to refer to it later. Your current backup may have become damaged, or may contain the same damage currently in your Great Plains system. Making an additional backup before you restore a previous backup will ensure that you’ll be able to restore your data to its current state, if the backup that you restore is also damaged. Make this backup on unused CDs or tape, not over a backup you have on hand.

2. Consult your reseller or qualified installer, or the manual for your backup utility, for information on how to restore a backup.

3. Reenter information entered after the backup was made, because any newer records were erased when the backup was restored.

4. The Table Descriptions window (Tools >> Resource Descriptions >> Tables) contains detailed information about each of the tables in Great Plains. This information can help you reenter data by providing the following:

  • The display name, technical name, physical name, and table group for each table
  • The reports containing information from each table. Use the reports listed to determine which data is missing, or as a source of the data you’ll need to reenter. The sample reports for each module also lists each report and the tables from which it draws data.
  • The window used to enter information in the table. To determine the physical and table groups accessed by a window, use the procedures in the Resource Descriptions documentation.

On rare occasions, you may not be able to reenter information into every table. Some records are created through processes such as posting or aging, and this information can’t be reentered manually in a window. If you were unable to reenter some of your accounting information, reports using non-editable tables, such as history tables, could be inaccurate until the end of the year, or until the next time you clear history.

5. If restoring a backup was unsuccessful and the original problems continue to occur, or if you don’t have a current backup, you may need to clear data and start over. Please call us before you reach this point!

Restoring Data

You can back up data for one company at a time. We recommend scheduling to back up company data on a regular basis. You also should back up the system database on a regular basis. The system database includes information about how many companies you have set up and where information is stored for those companies. Use the Restore Company window to restore data from a backup file.

Only the system administrator can open the Restore Company window and restore data. If you have Great Plains installed on a server, you must restore data on the server.

To restore data:

1. Open the Restore Company window. (File >> Restore)

2. Select the company to restore, or select System Database to restore system data.

3. Enter the path and file name of the backup file to restore from.

4. Click OK to restore data from the backup. The window will be closed and a message will appear when data has been restored.

Troubleshooting

Before you call support (Great Plains or Computer Resources), have the answers to the following questions ready to help your support specialist quickly narrow down the source of the problem you’ve experienced.

  • What is the exact error message?
  • When did the error first occur?
  • What task were you attempting to perform at the time you received the error message?
  • Has the task been completed successfully in the past?
  • What is the name of the window you are you working in?
  • What have you done so far to attempt to fix the problem?
  • Have you performed any of the table maintenance procedures such as check links?
  • If you have performed table maintenance procedures and received error messages, what kind of messages?
  • Does the problem occur in another company?
  • Does the problem occur on another workstation?
  • Does the problem occur for more than one user?
  • What versions of software are you using? Verify the version numbers for Great Plains, your database software, and Windows. Also note service packs.
  • Are you using an integrating product with Great Plains?
  • Have you imported any data?