How to transfer an existing Microsoft Dynamics GP, Microsoft Great Plains, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server
INTRODUCTION
This article describes how to transfer an existing Microsoft Dynamics GP, Microsoft Business Solutions - Great Plains, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or Microsoft SQL Server 2005. The article also describes how to maintain the user logins and databases.
MORE INFORMATION
Notes
|
• |
We strongly recommend that you perform the steps that are listed in the "Transfer instructions" section in a test environment before you perform the steps in a production environment. |
|
• |
The Microsoft SQL Server installation on the old server and on the new server can be Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server Desktop Engine (also known as MSDE 2000), Microsoft SQL Server 2005, or Microsoft SQL Server 2005 Express. |
|
• |
This article uses "the financial application" to refer to Microsoft Dynamics GP, Microsoft Great Plains, Microsoft Small Business Financials, and Microsoft Small Business Manager. |
Transfer instructions
|
1. |
On the old server, copy the following Capture_Logins.sql script to the local hard disk. |
|
2. |
On the old server, run the Capture_Logins.sql script to capture all SQL Server logins and password information. All SQL Server logins that are used by the financial application, by Microsoft Business Solutions - FRx, by Personal Data Keeper, or by any other application that is using the SQL Server installation on the old server will be captured. Follow these steps, based on the SQL Server that tools you use:
|
• |
If you use SQL Server Management Studio, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio. |
|
b. |
In the Connect to Server window, follow these steps:
|
1. |
In the Server name box, type the name of the server that is running SQL Server. |
|
2. |
In the Authentication box, click SQL Authentication. |
|
3. |
In the Login box, type sa. |
|
4. |
In the Password box, type the password for the sa user, and then click Connect. |
|
|
c. |
Click File, point to Open, and then click File. |
|
d. |
In the Look In list, click the Capture_Logins.sql script that you copied to the local hard disk in step 1, and then click Open. |
|
e. |
In the Connect to Database Engine window, follow these steps:
|
1. |
In the Server Name box, type the name of the old server that is running SQL Server. |
|
2. |
In the Authentication box, click SQL Authentication. |
|
3. |
In the Login box, type sa. |
|
4. |
In the Password box, type the password for the sa user, and then click Connect. |
|
|
f. |
Click Query, point to Results to, and then click Results to File. |
|
g. |
Click Query, and then click Execute. |
|
h. |
In the Save Results window, follow these steps:
|
1. |
In the Save in list, click the location where you want to save the results of the script. |
|
2. |
In the File name box, type SQLLOGINS.sql, and then click Save. |
|
|
|
• |
If you use Query Analyzer, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer. |
|
b. |
In the Connect to SQL Server window, follow these steps:
|
1. |
In the SQL Server box, type the name of the old server that is running SQL Server. |
|
2. |
In the Connect using area, click SQL Server Authentication. |
|
3. |
In the Login name box, type sa. |
|
4. |
In the Password box, type the password for the sa user, and then click OK. |
|
|
c. |
Click File, and then click Open. |
|
d. |
In the Open Query File window, in the Look In list, click the Capture_Logins.sql script that you copied to the local hard disk of the old server in step 1, and then click Open. |
|
e. |
Click Query, and then click Results to File. |
|
f. |
Click Query, and then click Execute. |
|
g. |
In the Save Results window, follow these steps:
|
1. |
In the Save in list, click the location where you want to save the results of the script. |
|
2. |
In the File name box, type SQLLOGINS.sql, and then click Save. |
|
|
|
• |
If you use MSDE 2000 and if no SQL Server tools are available, follow these steps:
|
a. |
On the server that is running MSDE, click Start, click Run, type cmd, and then click OK. |
|
b. |
At the command prompt, type the following script, and then press ENTER to run the script. Replace SERVERNAME with the name of the server that is running MSDE 2000. Replace SAPASSWORD with the password for the sa user.
OSQL –S SERVERNAME –U sa –P SAPASSWORD –i
“C:\Capture_Logins.sql” –o “c:\SQLLOGINS.sql” -n -w 500 |
|
c. |
Type exit, and then press ENTER. |
|
d. |
Right-click Start, and then click Explore. |
|
e. |
On drive C, open the SQLLOGINS.sql file to make sure that the script was created successfully. |
|
|
|
3. |
Make a full backup of the DYNAMICS database and all company databases on the old server.
Note On the server that is running SQL Server, MSDE 2000, or SQL Server 2005 Express, start the financial application, click File, and then click Backup to make a backup of each database.
You can also create a backup by using SQL Server Management Studio, Enterprise Manager, Query Analyzer, or the Support Administrator Console. |
|
4. |
On the old server, generate a SQL script for each SQL Server Agent Job that is currently scheduled and for each SQL Server Agent Operator that is currently set up. Follow these steps, based on the SQL Server tools that you use.
Note These steps apply to SQL Server Standard, to SQL Server Enterprise, or to SQL Server Workgroup Edition.
|
• |
If you use SQL Server Management Studio, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio. |
|
b. |
In the Connect to Server window, follow these steps:
|
1. |
In the Server name box, type the name of the server that is running SQL Server. |
|
2. |
In the Authentication box, click SQL Authentication. |
|
3. |
In the Login box, type sa. |
|
4. |
In the Password box, type the password for the sa user, and then click Connect. |
|
|
c. |
In the Object Explorer pane, expand SQL Server Agent, and then expand Jobs to view all available jobs.
Note If the SQL Server Agent is not started, right-click SQL Server Agent, and then click Start. |
|
d. |
Right-click a job, point to Script Job as, point to Create To, and then click File. |
|
e. |
In the Select a File window, select the folder where you want to save the script, and then type a file name. Click OK. |
|
f. |
Repeat steps c through e for all jobs. |
|
g. |
In the Object Explorer pane, expand SQL Server Agent , and then expand Operators to view all Operators that are currently set up. |
|
h. |
Right-click an operator, point to Script Operator as, point to Create To, and then click File. |
|
i. |
In the Select a File Window, select the folder where you want to save the script, and then type a file name. Click OK. |
|
j. |
Repeat steps g through i for all operators. |
|
|
• |
If you use Enterprise Manager, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager. |
|
b. |
Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the name of the server that is running SQL Server. |
|
c. |
Expand Databases, expand Management, and then expand SQL Server Agent. |
|
d. |
Right-click Jobs, point to All Tasks, and then click Generate SQL Script. |
|
e. |
In the Generate SQL Script window, select the folder where you want to save the script in the Save In list. |
|
f. |
In the File name box, type a name for the script, and then click Save. |
|
g. |
Click OK to generate the script. |
|
h. |
Right-click Operators, point to All Tasks, and then click Generate SQL Script. |
|
i. |
In the Generate SQL Script window, select the folder where you want to save the script in the Save In list. |
|
j. |
In the File name box, enter a name for the script, and then click Save. |
|
k. |
Click OK to generate the script. |
|
|
|
5. |
In Windows Explorer, copy the SQLLOGINS.sql script that you created in step 2, the backup files that you created in step 3, and the SQL Server Agent Job and SQL Server Agent Operator scripts that you created in step 4 from the old server to the hard disk on the new server.
Note If you are using the same server, you do not have to complete this step. |
|
6. |
Install SQL Server on the new server if it is not already installed.
Notes
|
• |
Make sure that you use the same sort order that was used on the old server. To obtain the sort order that was used on the old server, run the following script against the master database in the SQL Server Management Studio, in Query Analyzer, or in the Support Administrator Console:
sp_helpsort
The following list shows the SQL Server sort orders that the financial applications support:
|
• |
Column to verify: Server Collation Default
Column contents: Latin1-General, binary sort
Column meaning: Binary Sort Order 50 |
|
• |
Column to verify: Server Collation Default
Column contents: Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
Column meaning: Dictionary Order Case Insensitive (DOCI) Sort Order 52 |
|
|
• |
If you are using the same server, install a new instance of SQL Server on the same computer. In the rest of this article, the term "new server" is used to refer to the new server that is running SQL Server or to the new instance of SQL Server on the old computer. |
|
• |
If you restore a database that was installed on the computer that is running SQL Server 7.0 or SQL Server 2000 and if you are moving the database to a computer that is running SQL Server 2005, you will have to update the database compatibility level for each database after the restore. To do this, follow these steps on the new server in SQL Server Management Studio:
|
a. |
In the Object Explorer area, expand Databases, right-click the database, and then click Options. |
|
b. |
In the Compatibility box, click to select the SQL Server 2005 (90) check box. |
|
|
|
7. |
On the new server, restore the DYNAMICS database from the backup file that you created in step 3. Follow these steps, based on the SQL Server tools that you use.
Note If you are using the same server, restore the databases on the new instance of SQL Server on the same computer.
|
• |
If you use SQL Server Management Studio, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio. |
|
b. |
In the Connect to Server window, follow these steps:
|
1. |
In the Server Name box, type the name of the new server that is running SQL Server. |
|
2. |
In the Authentication box, click SQL Authentication. |
|
3. |
In the Login box, type sa. |
|
4. |
In the Password box, type the password for the sa user, and then click Connect. |
|
|
c. |
In the Object Explorer area, right-click Databases, and then click Restore Database. |
|
d. |
In the Destination for restore area, type DYNAMICS in the To database box. |
|
e. |
In the Source for restore area, click From Device, and then click the ellipsis button to open the Specify Backup window. |
|
f. |
In the Backup Media list, click File, and then click Add to open the Locate Backup Files window. |
|
g. |
In the Select the file area, click the backup file for the DYNAMICS database that you backed up in step 3, click OK, and then click OK. |
|
h. |
In the Select the backup sets to restore area, click to select the Restore check box next to the backup that you want to restore. |
|
i. |
In the Select a Page area, click Options, and then click to select the Overwrite the existing database check box. |
|
j. |
In the Restore the database files as area, change the Restore As column so that the data file and the log file use the correct paths on the new server.
Note The default paths for SQL Server 2005 are the following.
%systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Data.mdf
%systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Log.ldf
You can find these files by using Windows Explorer. |
|
k. |
Click OK. |
|
|
• |
If you use Enterprise Manager, follow these steps:
|
a. |
Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager. |
|
b. |
Expand Microsoft SQL Servers, expand SQL Server Group, expand the name of the new server. |
|
c. |
Right-click Databases, point to All Tasks, and then click Restore Database. |
|
d. |
In the Restore as database box, type DYNAMICS. |
|
e. |
In the Restore area, click to check From device, and then click Select Devices. |
|
f. |
In the Choose Restore Devices window, click Add. |
|
g. |
In the Choose Restore Destination Window, click the ellipsis button, locate and then click the backup file of the DYNAMICS database that you backed up in step 3, click OK, click OK, and then click OK again. |
|
h. |
Click the Options tab, and then click to select the Force restore over existing database check box. |
|
i. |
| | |