ACE Dynamics GP, CRM and SharePoint Microsoft Partner: December 2010

Three elements:
1) SQL Server database backups: Make sure to backup your Dynamics database and all of your production company databases. Always make sure to include the Dynamics database as part of your company database backups, since it can contain very valuable information, such as Business Portal data, custom SmartLists created using SmartList Builder, Analytical Accounting setup tables, and user access and GP security settings.

2) Dynamics GP application backups: At a minimum, make sure to backup all of your modified forms and reports dictionaries, the Dynamics.set file, VBA files, and any files installed in the AddIns and Data folders. An easy way to do this is to backup the entire Dynamics GP application directory--it's easy, fast, and often more reliable or less error prone than selecting individual files. If you are using shared dictionaries that are stored in a different location, make sure to backup those files as well.

3) SQL Server security settings backup: If your SQL server fails and you need to migrate to a new SQL server, it can be very helpful to have a backup of your SQL logins and users. The security settings can be scripted, allowing you to quickly recreate a large number of SQL logins. If you only have a few GP users, this step may not be worthwhile, but if you have more than 20 users, I would recommend running the script occasionally to save time in case you do lose your SQL Server.

Backup Plan Options:
1) SQL Server databases: Ideally, the Dynamics and production GP company databases should be set to use Full recovery model, allowing for point in time recovery. Under the Full recovery model, the production databases should typically have a nightly full backup, and several transaction log backups should run throughout the day, typically every 1-2 hours during business hours. The frequency of the transaction log backups will reflect how much data you are willing to lose in case of a serious issue with the SQL Server. So if your transaction log backups run every 2 hours, you could possibly lose up to 2 hours worth of data entry or posting. If you are willing or able to tolerate the loss of an entire day's worth of data, you could do only nightly full backups and no transaction log backups, or you could switch to Simple recovery model with nightly backups. Using the SQL Server Maintenance Plan Wizard, it is very easy to setup a scheduled backup job that will backup your databases and transaction logs--it takes less than 60 seconds to setup and test a basic backup plan.
If you use the Maintenance Plan Wizard, make sure that you create and enable a schedule.
It is not recommended to append your backups to a single backup file. The file will quickly become very large and too cumbersome to archive.
For ease of organizing and locating backup files, it is recommended to create a separate directory for each database.
When you setup your nightly backup job and transaction log backups, you should save the backup files to a separate physical disk than your database files. If your data drives fail, you don't want your backups to be on those drives as well.

It is recommended that you perform nightly standard SQL Server backups to disk first, and keep at least 3-5 days of backups on disk so that they are easily accessible. You can then backup those SQL backup files to tape or an archive disk for longer term storage. You should keep several months of monthly backups (at least 3-6), since you may have a situation where you need to recover something that was older than a 2 week or monthly backup rotation.

It is not recommended to use backup software to backup SQL databases directly to tape. It theoretically works, but when you need to urgently recover your GP databases, the last thing you want to do is have to rely on tapes and 3rd party backup software as your only backup source. Tape is fine as one element of an overall backup strategy, but it is also strongly recommended that you always have some SQL backup files that are accessible on disk.

2) Dynamics GP application: This backup doesn't have to be performed very often--typically only after a customization is added, a new GP module or 3rd party product is added, or GP is upgraded as part of a service pack or hotfix. It is very easy to forget to perform this backup after all of those events, so it is a good practice to just schedule a monthly backup.

3) SQL security settings: Run the "Capture_Logins.sql" script (which can be obtained from your Microsoft Dynamics GP partner) to backup all SQL Server logins and password information. If you have a lot of GP users, this backup can be very helpful and can save a lot of time for a server restore, migration, or simply setting up a new test or QA server. If you ever have to urgently recover your GP environment to a new SQL Server, having the logins scripted can reduce your stress and make a recovery much smoother. Like the Dynamics GP application backups, this step does not need to be performed very often. As long as you have the majority of your users in the script, you can easily add a few new users if necessary.

Once you have all of these backups setup and running successfully, make sure to have an on-site and off-site archiving strategy. You could buy two 1TB external drives and rotate them weekly to a relatively safe, relatively secure, off-site location. It's easy to find very real news stories about floods, hurricanes, and earthquakes, to know that your building, server, and data are not immune from being completely destroyed. Online backup services have become so inexpensive and convenient that they are also an option for certain types and amounts of data. One caveat, however, is that if you are using the Dynamics GP Human Resources or Payroll modules, you will want to be especially careful with your backups. States such as California have laws that govern the storage and loss of private data, and common courtesy dictates that you care for SSNs and employee info, so if you do have sensitive data, you may want to consider encryption options for your off site backups.
TEST YOUR BACKUP PLAN USING A RECOVERY PLAN!

Here's how you can test your backups from scratch in less than two hours:

1) Install a new instance of SQL Server on a Windows server machine (it can be desktop with Windows Server 2003)
2) Install Dynamics GP on the machine
3) Copy your backup of the GP application directory and overwrite the GP files on your machine
4) Restore your most recent GP database backups
5) Run the SQLLOGINS.sql script that you generated from Capture_Logins.sql
6) Launch GP, login as both sa and a standard user, and verify the data and users

And once that test system has been setup, future tests of your recovery plan would probably take less than 30 minutes, as you should only need to complete steps 3, 4, 5, and 7.

Below are two articles which can be found on Customersource:
Article 1: https://mbs.microsoft.com/customersource/documentation/howtodocuments/msd_gprecommendedmaintenancesqldatabases.htm?printpage=false&stext=875193
Recommended Maintenance with Microsoft SQL Server for Microsoft Dynamics GP Databases
Last Modified 8/5/2010
Posted 4/21/2010
Database maintenance helps keep the inner workings of Microsoft SQL Server and the Microsoft Dynamics GP databases running at their peak level. We recommend that you use the following SQL Server maintenance procedures:
Use database consistency check commands
Update statistics
Recompile stored procedures
We recommend that you perform these maintenance procedures weekly for the DYNAMICS database and all company databases. However, you can vary this frequency based on your environment and the activity each database receives. We also recommend that you stagger these maintenance procedures throughout the week to handle lots of data.
Note 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 or SQL Server Management Studio. To do this, follow these steps:
1. If you use Microsoft SQL Server 2000, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

If you use Microsoft SQL Server 2005 or 2008, click Start, point to Programs, point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. In SQL Query Analyzer or SQL Server Management Studio, 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.

Note DBCC CHECKCATALOG is included in DBCC CHECKDB for Microsoft SQL Server 2005 or later.
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 or SQL Server Management Studio so that the maintenance process runs automatically.
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 by using the Reindex.sql script. You can find the Reindex.sql script in the default path for Microsoft Dynamics GP as C:\Program Files\Microsoft Dynamics\GP\SQL\Utility\0 folder.

You can also schedule this command within SQL Server Enterprise Manager or SQL Server Management Studio so that the maintenance process runs automatically.
Update 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 by using SQL Query Analyzer or SQL Server Management Studio. You can find the UpdatSta.sql script in the default path for Microsoft Dynamics GP as C:\Program Files\Microsoft Dynamics\GP\SQL\Utility\0 folder.
You can run the update statistics process within Microsoft Dynamics GP. 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 and product for Product box.
3. Select all the tables for that database by pressing CTRL+A within the table listing.
4. Click Update Statistics, and then click Process.
You can configure SQL Server to run the update statistics process automatically within SQL Server Enterprise Manager or SQL Server Management Studio. 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 Plans.
Recompile 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 by using SQL Query Analyzer or SQL Server Management Studio. You can find the Recomp.sql script in the default path for Microsoft Dynamics GP as C:\Program Files\Microsoft Dynamics\GP\SQL\Utility\0 folder.
You can also recompile stored procedures within Microsoft Dynamics GP. 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 and product for Product box.
3. Select all the tables for that database by pressing CTRL+A within the table listing.
4. Click Recompile, and then click Process.
You can recompile stored procedures by scheduling the sp_recompile procedure by using SQL Server Agent Jobs. For more information about steps to create a job within SQL Server Enterprise Manager or SQL Server Management Studio, see SQL Server Books Online. To download SQL Server Books Online, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97
Note If you receive any consistency or allocation errors when you are running these SQL maintenance procedures, you must contact Microsoft SQL Server support. This is not included in the support plan for Microsoft Dynamics GP and it would be an additional cost. Microsoft SQL Server support can be contacted at 1-800-936-5800.
Database Maintenance Procedure Definitions
The following is a list of definitions of database maintenance procedures. For more information, see SQL Server Books Online. To download SQL Server Books Online, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97
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.

Note DBCC CHECKCATALOG is included in DBCC CHECKDB for Microsoft SQL Server 2005 or later.
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 about 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.
________________________________________
APPLIES TO
System Manager, when used with:
o Microsoft Dynamics GP 2010
o Microsoft Dynamics GP 10.0
o Microsoft Dynamics GP 9.0
Article 2: https://mbs.microsoft.com/partnersource/deployment/documentation/howtoarticles/MSD_GPSLInfoDatabaseMaintenancePlans
Information About Database Maintenance Plans for Microsoft Dynamics GP or Microsoft Dynamics SL on Microsoft SQL Server 2000, Microsoft SQL Server 2005 or Microsoft SQL Server 2008
Last Modified 9/18/2010
Posted 9/17/2010
This article discusses recommendations for setting up a database maintenance plan on Microsoft SQL Server 2000, Microsoft SQL Server 2005 or Microsoft SQL Server 2008, for Microsoft Dynamics GP, Microsoft Dynamics SL, or Microsoft Business Solutions - Solomon.
You can set up a weekly maintenance plan for databases in Microsoft Dynamics GP, in Microsoft Dynamics SL, or in Microsoft Business Solutions - Solomon. However, the frequency that you select depends on the environment and on how much activity occurs within the databases. We recommend that you examine the output for warning signs of possible problems after you set up a maintenance plan.
Microsoft SQL Server 2000
To set up a database maintenance plan on Microsoft SQL Server 2000, follow these steps:
1. In SQL Server Enterprise Manager, open the Database Maintenance Plan Wizard. To do this, right-click the database, click All Tasks, click Maintenance Plan, and then click Next.
2. Click to select the databases that you want to include in your maintenance plan, and then click Next.
Notes
o For Microsoft Dynamics GP, make sure that you include all the DYNAMICS and Company databases.
o For Microsoft Dynamics SL, make sure that you include all the System and Application databases.
3. Click Reorganize Data and Index Pages, click Change free space per page percentage to, scroll to 10, click Change, enter a schedule and timeframe for this task, click OK and then click Next.
4. Click Check Database Integrity, click Include indexes, click Change, enter a schedule and time frame for this task, click OK, and then click Next.
5. Click Back up the database as part of the maintenance plan, click Verify the integrity of the backup when complete, click Tape or Disk to select the type of backup, click Change, enter a schedule for the full backup, click OK, and then click Next.
Note If you select Disk as the backup type, the Specify Backup Disk Directory window appears. You can then select the default backup directory or a specific directory location. If you click the Use this directory option, you must specify a directory.
Note To create subdirectories for each database, click Create a subdirectory for each database. To set up SQL Server to remove old files, click Remove files older than, and then enter a time period.
6. Click to select options to configure the Transaction Log backup, and then click Next. These options are the same as the options in step 4.
7. If you want to generate a report, click Write report to a text file in directory, enter a location for the file, click Delete text reports older than, enter a time period, and then click Next.
8. For the local server, click Write history to the msdb.dbo.sysdbmaintplan_history table on this server, click Limit rows in the table to, type or scroll to select the number of rows for this plan, click Next, and then click Finish.
9. Click Management, click SQL Server Agent, and then click Jobs. Verify that you have four jobs: two jobs for database maintenance and two jobs for backup procedures. These jobs should run at the scheduled times you entered.
Microsoft SQL Server 2005
To set up a database maintenance plan on Microsoft SQL Server 2005, follow these steps:
1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the "Connect to Server" window, click SQL Server Authentication in the Authentication list, and then type the sa user name and password. Click Connect.
3. In the Object Explorer area, double-click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard starting page, click Next.
5. In the "Select A Target Server" window, type a name for the maintenance plan in the Name field, and then type the SQL server name in the Server field.
6. Click Use SQL Server Authentication, type the sa username and password, and then click Next.
7. In the Select Maintenance Tasks window, click to select the check box for all the following tasks:
o Check Database Integrity
o Reorganize Index
o Update Statistics
o Back Up Database (Full)
Note To schedule transaction log backups, click to select the Back Up Database (Transaction Log) check box.
8. Click Next.
9. In the Select Maintenance Task Order window, accept the default order, and then click Next.
10. In the Databases list, click to select the check box for each Microsoft Dynamics GP database or for each Microsoft Dynamics SL database, click OK, and then click Next.
11. In the Define Reorganize Index Task window, click to select the check box for each Microsoft Dynamics GP database that is in the Databases list, click OK, and then click Next.
12. In the Define Update Statistics Task window, click to select the check box for each Microsoft Dynamics GP database that is in the Databases list, click OK, and then click Next
13. In the Define Back Up Database (Full) Task window, click to select the check box for each Microsoft Dynamics GP database that is in the Databases list, and then click OK.
14. In the Destination area, click the option button for the appropriate destination, and then click Next.
15. In the Select Plan Properties window, click Change, and then enter the schedule that you want for the maintenance plan. For example, you may want to schedule maintenance during non-business hours. Click OK, and then click Next.
16. In the Select Report Options window, accept the default entries, and then click Next.
17. In the Complete the Wizard window, click Finish. After the maintenance plan is created, click Close.
18. If you want to configure the maintenance plan to delete backup files that are older than a specific date, follow these steps:
o In the Object Explorer area, double-click Management, double-click Maintenance Plans, right-click the maintenance plan that you created, and then click Modify.
o In the Toolbox area at the bottom of the Object Explorer, drag Maintenance Cleanup Task to the right pane.
o In the right pane, double-click Maintenance Cleanup Task.
o In the File Location area, click Delete Specific File. In the File name field, type the path of the location where the backup file is located.
Note If you have backup files for each database that are located in separate folders, you must create a Maintenance Plan Cleanup Task for each database folder.
o To delete full backup files, type BAK in the File Extension field. To delete Transaction Log backup files, type TRN in the File Extension field. To delete both types of files, you must create a Maintenance Plan Cleanup Task for each type of file.
o In the File Age area, click the appropriate option to set the schedule on which the files will be deleted, and then click OK.
o In the right pane, click Back Up Database (Full). A green arrow now appears at the bottom of the window.
o Drag the green arrow to the Maintenance Cleanup Task. A green arrow will connect Back Up Database (Full) to Maintenance Cleanup Task.
o Click File. Then click Save Selected Item.
Microsoft SQL Server 2008
To set up a database maintenance plan on Microsoft SQL Server 2008, follow these steps:
1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, type the sa user name and password, and then click Connect.
3. In the Object Explorer area, double-click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard starting page, click Next.
5. In the Select Plan Properties window, type a name for the maintenance plan in the Name field, type a descriptive comment in the Description field, and then select Single schedule for the entire plan or No schedule.
6. In the Job Schedule Properties window, click the Change button for Schedule, set up a schedule type of "Recurring as Enabled" that has the frequency parameters that meet your maintenance needs, and then click OK.
7. Click Next to move to the Select Maintenance Tasks window.
8. In the Select Maintenance Tasks window, click to select the check box for each of the following tasks:
o Check Database Integrity
o Reorganize Index
o Update Statistics
o Back Up Database (Full)
9. Click Next.
10. In the Select Maintenance Task Order window, accept the default order, and then click Next.
11. In the Databases list, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
12. In the Define Reorganize Index Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
13. In the Define Update Statistics Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
14. In the Define Back Up Database (Full) Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, review the remaining backup parameters, and then click Next.
15. In the Select Report Options window, accept the default entries, and then click Next.
16. In the Complete the Wizard window, click Finish. After the maintenance plan is created, click Close.
17. If you want to configure the maintenance plan to delete backup files that are older than a specific date, follow these steps:
o In the Object Explorer area, double-click Management, double-click Maintenance Plans, right-click the maintenance plan that you created, and then click Modify.
o In the Toolbox area at the bottom of the Object Explorer, drag Maintenance Cleanup Task to the right pane.
o In the right pane, double-click Maintenance Cleanup Task.
o In the File Location area, click Delete Specific File.
o In the File name field, type the path of the location where the backup file is located.
Note If you have backup files for each database and these files are located in separate folders, you must create a Maintenance Plan Cleanup Task for each database folder.
o To delete full backup files, type BAK in the File Extension field. To delete Transaction Log backup files, type TRN in the File Extension field. To delete both types of files, you must create a Maintenance Plan Cleanup Task for each type of file.
o In the File Age area, click the appropriate option to set the schedule according to which the files will be deleted, and then click OK.
o In the right pane, click Back Up Database (Full). A green arrow now appears at the bottom of the window.
o Drag the green arrow to Maintenance Cleanup Task. A green arrow will connect Back Up Database (Full) to Maintenance Cleanup Task.
o Click File, and then click Save Selected Item.
________________________________________
APPLIES TO
System Manager
Microsoft Dynamics GP 10.0
Microsoft Dynamics GP 9.0
Microsoft Dynamics GP 2010
Microsoft Dynamics SL 6.5
Microsoft Business Solutions-Solomon 6.0
Microsoft Dynamics SL 7.0