Great Plains Maintenance and Recovery Procedures
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 on page 12 before contacting us.
Great Plains Maintenance Procedures............1
Data Recovery
................................................8
Recovering Damaged Data
.................................8
Checking Links
.................................................9
Reconciling Tables ...........................................10
Restoring Backups ...........................................10
Restoring Data
.................................................11
Troubleshooting
................................................12
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.
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.
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:
Item |
Frequency |
Minimum Time to Keep Backup |
|
Transaction log |
Twice a Day |
Two Weeks |
|
Database and Transaction Log |
Every Day |
Two Months |
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.
It is important to back up a database either before or after the following procedures:
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.
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:
o DBCC CHECKCATALOG (DBNAME)
When you use this statement, replace DBNAME with the name of your database, such as DYNAMICS.
o 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.
o 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.
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.
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.
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.
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
Once you’ve determined that a problem exists, you can use the following questions to direct your troubleshooting effort.
Remember that you have security options within SQL Server and MSDE, and within Great Plains.
Do not delete dictionaries. Renaming files allows you to restore these files later, if necessary.
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.
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.
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.
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.
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.
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
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.
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 Statistics and 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 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.
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.
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.
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 Backups or Restoring Data, below.
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.
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!
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.
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.
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?
901-382-1MEG (1634) Serving Memphis, Mississippi, Arkansas, Tennessee, and the Mid-South
|
