You are basically on the right track. Here is a document from the MBS website on how to transfer Great Plains from one server to another. This will work if you are running SQL 2K.
1. Load Microsoft SQL Server with the same sort order as the older server and Great Plains on the new computer.
2. Execute the Capture_Logins_70_2000 script against the DYNAMICS database on the old server.
/*
** Capture_Logins_70_2000.sql
**
** Creates a table called RecoveryMaster that stores the username and password.
** Insert the name and password from the master database for all Dynamics logins.
** This table will be used for recovery on the new server.
**
**
*/
/* Always recreate the RecoveryMaster table. */
SET QUOTED_IDENTIFIER OFF
if exists (select * from DYNAMICS..sysobjects
where id = Object_id('DYNAMICS..RecoveryMaster') and type = 'U')
begin drop table DYNAMICS..RecoveryMaster end
go
CREATE TABLE DYNAMICS..RecoveryMaster (
name char(15) not null unique,
password varbinary(256) null,
DEX_ROW_ID int IDENTITY(1,1) not null )
go
/* Now insert the information */
insert DYNAMICS..RecoveryMaster (name, password)
select name, password from master..sysxlogins
where name in (select USERID from DYNAMICS..SY01400)
and name not in ('sa', 'DYNSA')
3. Make a backup of the DYNAMICS database and Company databases on the old server.
4. Copy the backup file from the old server to a local drive on the new server where Microsoft SQL Server is installed.
5. Set up an ODBC connection to point to the new server.
6. Create a new DYNAMICS database by using Great Plains Utilities - be sure to use the same number of Account segments and digits as before (if not sure, query the SY003001 and SY00302 tables). Create the new Company database(s) on the new server using Great Plains Utilities.
7. In Enterprise Manager, restore the DYNAMICS database and each Company database backup file created above in Step 3. To do this, you must follow the instructions below:
/*
** Drop_Users_Dynamics.sql
** Drops all users from the DYNAMICS database.
** Drops the DYNGRP and recreates it.
*/
declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */
/*
** Remove all users from the DYNAMICS DYNGRP group and delete the group.
*/
SET QUOTED_IDENTIFIER OFF
use DYNAMICS
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')
declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use DYNAMICS
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in DYNAMICS.
*/
use DYNAMICS
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end
10. Execute the Drop_User_Company against each of your COMPANY databases on the new server. This script is for both SQL 7.0 and SQL 2000.
Note - You will need to replace %Companydb% with your Company database name for this script.
/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/
declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */
/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF
use %Companydb%
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')
declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in all company databases.
*/
use %Companydb%
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end
11. Execute the Create_SQL_Logins_70_2000.sql script against the DYNAMICS database on the new server.
/*
** SM_Create_SQL_Logins_70_2000.sql
**
** Drop and Recreates all Dynamics logins based off of the RecoveryMaster table.
** Adds users to the DYNGRP for the Dynamics database.
**
*/
declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */
/* drops all Dynamics logins. */
SET QUOTED_IDENTIFIER OFF
use master
declare G_cursor CURSOR for select "if exists (select name from master..syslogins
where name = '" + USERID + "') begin exec('sp_droplogin ["
+ USERID + "]') end" from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Recreate all Dynamics logins.
*/
use master
declare G_cursor CURSOR for select "sp_addlogin [" + USERID + "], NULL, DYNAMICS"
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/* Adds users to the DYNGRP in the Dynamics database. */
use DYNAMICS
declare G_cursor CURSOR for select "sp_adduser " + USERID + ", [" + USERID + "], 'DYNGRP'"
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Update the login passwords with the backed up values from the production server.
** This has to be done in 'allow updates' mode because the syslogins system table
** is being directly updated.
*/
exec sp_configure 'allow updates', 1
exec ("reconfigure with override")
exec ("update m
set m.password = rm.password
from master..sysxlogins m, DYNAMICS..RecoveryMaster rm
where m.name = rm.name")
exec sp_configure 'allow updates', 0
exec ("reconfigure with override")
12. Execute the Add_Users_Company against each of your COMPANY databases on the new server. This script is for both SQL 7.0 and SQL 2000.
Note - You will need to replace %Companydb% with your Company database name for this script.
/*
** Add_Users_Company.sql
**
** Adds users to the DYNGRP for the Company database.
**
** NOTE: will need to replace %Companydb% with the company database name.
**
*/
declare @cStatement varchar(255) /* Misc exec string */
/*
** Add users back to the appropriate DYNGRP groups for the company database.
*/
SET QUOTED_IDENTIFIER OFF
use %Companydb%
declare G_cursor CURSOR for select "if exists(select * from
DYNAMICS..SY60100 a,DYNAMICS..SY01500 c,DYNAMICS..RecoveryMaster u
where u.name=a.USERID and a.CMPANYID=c.CMPANYID and c.INTERID=""%Companydb%""
and u.name='"+rtrim(USERID)+"')
exec(""sp_adduser ["+rtrim(USERID)+"],["+rtrim(USERID)+"],'DYNGRP'"")"
from DYNAMICS..SY01400
where USERID in (select USERID from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
13. Execute the GRANT70.SQL (SQL 7.0 & SQL 2000) script against the DYNAMICS and ALL COMPANY databases on the new server.
/*
** Grant70.sql
**
*/
/*Count : 1 */
declare @cStatement varchar(255)
declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where type = 'P'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
14. Execute LOCKS.SQL in Query Analyzer to recreate the dex_lock and dex_session tables in the Tempdb database. You can find this script in Windows Explorer in Great Plains folder | SQL folder | Utils folder | LOCKS.SQL.
15. Load any third party products or additional products (such as Human Resources and Fixed Assets, etc.) that are needed at the new server.
