Do You Know by GURUS: Copy Database

Showing posts with label Copy Database. Show all posts

Using SanScript for Retrieving System Database Name in Microsoft Dynamics GP

To get the System DB name when integrating with Dynamics, use the following sanscript code:

---------------------------------------------------------------------------------------------
local integer companyid;
local integer dictionary;
local integer file_series;
local integer logical_file;

companyid = DYNAMICS; {constant with value 0 }
dictionary = DYNAMICS;
file_series = TABLESSERIES_SYSTEM; { constant with value 7 }
logical_file = 0;

set sDatabase to SQL_GetDbName(companyid, dictionary, file_series, logical_file, fSuccess).
---------------------------------------------------------------------------------------------

This routine will then return the string name of the System database. This is all done by accessing the SY_Pathnames table and retrieving the column Database Name.

While it probably is much more proper to do this, it probably isn't worth the effort to get the system series DB from code because installation hardcodes the DB to "DYNAMICS" in all current cases. But it is possible in the future this could change so this isn't a bad idea.

Database Maintenance Plan for Microsoft Dynamics GP on Microsoft SQL Server 2008

For setting up a database maintenance plan on Microsoft SQL Server 2008, for Microsoft Dynamics GP.

You can set up a weekly maintenance plan for databases in Microsoft Dynamics GP. 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.

To set up a database maintenance follow these steps:

Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
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.
In the Object Explorer area, double-click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
On the Maintenance Plan Wizard starting page, click Next.
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.
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.
Click Next to move to the Select Maintenance Tasks window.
In the Select Maintenance Tasks window, click to select the check box for each of the following tasks:
- Check Database Integrity
- Reorganize Index
- Update Statistics
- Back Up Database (Full)
Click Next.
In the Select Maintenance Task Order window, accept the default order, and then click Next.
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.
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.
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.
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.
In the Select Report Options window, accept the default entries, and then click Next.
In the Complete the Wizard window, click Finish. After the maintenance plan is created, click Close.
If you want to configure the maintenance plan to delete backup files that are older than a specific date, follow these steps:
- In the Object Explorer area, double-click Management, double-click Maintenance Plans, right-click the maintenance plan that you created, and then click Modify.
- In the Toolbox area at the bottom of the Object Explorer, drag Maintenance Cleanup Task to the right pane.
- In the right pane, double-click Maintenance Cleanup Task.
- 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 and these files are located in separate folders, you must create a Maintenance Plan Cleanup Task for each database folder.
- 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.
- 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.
- In the right pane, click Back Up Database (Full). A green arrow now appears at the bottom of the window.
- Drag the green arrow to Maintenance Cleanup Task. A green arrow will connect Back Up Database (Full) to Maintenance Cleanup Task.
- Click File, and then click Save Selected Item.

Bulk Copy Process (BCP) to Export Microsoft Dynamics GP Data into New Database

How to use the Bulk Copy Process (BCP) to export data and to import data by using Microsoft Dynamics GP.

Make a backup of your company database.
Copy and paste the following CreateBulkCopyOut.sql script into Microsoft SQL Query Analyzer.

/* Script to create bcp commands to export data for all tables. */

SET QUOTED_IDENTIFIER OFF

select 'bcp "TWO..' + name + '" out ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:

Replace TWO with the name of your company database.
Replace password with your sa password.
Replace SERVERNAME with the name of your instance of Microsoft SQL Server.

Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000), run the statement in Microsoft Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.

Run the script against the database and then save the results to a batch file. To do this, follow these steps:

If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder named BCPData, name this file Copyout.bat, and then click Save.
If you are using Support Administrator Console, click File, and then click Export. Create a folder and name it BCPData. Name the file Copyout.bat. Then click Save.

Copy and paste the following CreateBulkCopyIn.sql script into Query Analyzer.

/* Script to create bcp commands to import data for all tables. */

SET QUOTED_IDENTIFIER OFF

select 'bcp "TWO..' + name + '" in ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:

Replace TWO with the name of your company database.
Replace password with your sa password.
Replace SERVERNAME with the name of your instance of SQL Server.

Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using SQL Server 2000 Desktop Engine, run the statement in Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.

Run the script against the database and then save the results to a batch file.

If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder and name it BCPData. Name the file Copyin.bat, and then click Save.
If you are using Support Administrator Console, click File and then click Export. Create a folder and name it BCPData. Name the file Copyin.bat. Then click Save.

Perform a bcp command to move the data out of the company database. To do this, use the appropriate method.

Open the BCPData folder.
Double-click the Copyout.bat file.

Note The batch file starts the BCP process to copy the data from the database to a text file.

Delete the company from within Great Plains. To do this, log into Great Plains as the sa user. Click Tools, point to Setup, point to System and then click Company. Click the lookup glass to display all the companies listed. Select the company and then click Delete.

Note For versions of Microsoft Great Plains that are earlier than Microsoft Great Plains 8.0, delete the company. To delete the company, do the following: On the Setup menu, click System, and then click Company to delete the company.
Remove the database.

If you are using Microsoft SQL Server, open Enterprise Manager, expand the server name, expand Database, right-click the company database that you deleted in step 7, and then select Delete.

DROP DATABASE TWO

Re-create the company database and procedures. To do this, start Great Plains Utilities, log on as the sa user, and then click Create new company in the Additional Tasks dialog box.

Note Use the same company name as the one that you deleted in step 7.
After the company is created, the tables must be truncated.

If you are using Microsoft SQL Server, run the following Truncate_Table_Company.sql script in Query Analyzer.

o /* Script to remove all data from all user tables in the company database */

o SET QUOTED_IDENTIFIER OFF

o if exists (select * from sysobjects where name = 'RM_NationalAccounts_MSTR_FKC')

o ALTER TABLE dbo.RM00105

o DROP CONSTRAINT RM_NationalAccounts_MSTR_FKC

o Go

o declare @tablename char(255)

o DECLARE t_cursor CURSOR for

o select "truncate table " + name

o from sysobjects where type = 'U'

o set NOCOUNT on

o open t_cursor

o FETCH NEXT FROM t_cursor INTO @tablename

o while (@@fetch_status <> -1)

o begin

o if (@@fetch_status <> -2)

o begin

o exec (@tablename)

o end

o FETCH NEXT FROM t_cursor into @tablename

o end

o DEALLOCATE t_cursor

o GO

o ALTER TABLE dbo.RM00105 ADD

o CONSTRAINT RM_NationalAccounts_MSTR_FKC FOREIGN KEY

o (

o CPRCSTNM

o ) REFERENCES dbo.RM00101 (

o CUSTNMBR

o )

o GO

Move the data back into the company database. To do this, open the BCPData folder, and then double-click the Copyin.bat batch file. Running this batch file starts the process of moving the data back into the company database. When the data is moved back into the database, all indexes are created and verified for each table.

Note When the process is completed, the BCPData folder will contain .err files. If any one of these .err files is larger than 0 KB, the data was not imported for the company database successfully.

Additional steps

If you are using the BCP process to change your Microsoft SQL Server Sort Order, you must bulk copy data out of the DYNAMICS database and all Company databases.

Note Changing the Microsoft SQL Server Sort Order for the DYNAMICS database and for the Company database is not supported by Microsoft. This is only supported if MBS Professional Services makes the change for you.

To do this, follow steps 1 through 11. In step 10, you must also truncate the DYNAMICS database. To do this, use one of the following methods:

If you are using Microsoft SQL Server, run the following Truncate_Tables_Dynamics.sql script in Query Analyzer.

· /* ** **

· Truncate_Tables_Dynamics.sql

· function: Will remove all data from all user tables in the DYNAMICS database

· ** */

· SET QUOTED_IDENTIFIER OFF

· if exists (select * from sysobjects where name = 'orgEntity_SETP')

· ALTER TABLE dbo.ORG40100

· DROP CONSTRAINT orgEntity_SETP

· GO

· if exists (select * from sysobjects where name = 'orgRelation_MSTR')

· ALTER TABLE dbo.ORG00100

· DROP CONSTRAINT orgRelation_MSTR

· Go

· declare @tablename char(255)

· DECLARE t_cursor CURSOR for

· select "truncate table " + name

· from sysobjects where type = 'U'

· set NOCOUNT on

· open t_cursor

· FETCH NEXT FROM t_cursor INTO @tablename

· while (@@fetch_status <> -1)

· begin

· if (@@fetch_status <> -2)

· begin

· exec (@tablename)

· end

· FETCH NEXT FROM t_cursor into @tablename

· end

· DEALLOCATE t_cursor

· GO

· ALTER TABLE dbo.ORG40100 ADD

· CONSTRAINT orgEntity_SETP FOREIGN KEY

· (

· ENTYLVL

· ) REFERENCES dbo.ORG40000 (

· ENTYLVL

· )

· GO

· ALTER TABLE dbo.ORG00100 ADD

· CONSTRAINT orgRelation_MSTR FOREIGN KEY

· ( ENTITYID ) REFERENCES dbo.ORG40100 ( ENTITYID )

· GO