Copying Great Plains Databases for Server Transfer or Test Environment

If you've ever done a server transfer for Microsoft Dynamics GP (Great Plains) you'll know how much effort it is just to move the databases to the new server and run the SQL scripts against them as described in the KB article (878449): https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyqlytnsukzpxkmunvxxrmsqnmtquptkvo

We have a company with 80 company databases that we needed to copy into a test environment at our office to set up a complex report. There was no way I was going to do all those steps manually. Thus, the creation of a couple of handy SQL scripts to do the job for me.

The backup script - nothing spectacular here - it just sticks on our common file suffix.

/*
This script backs up all GP databases to a local or network location
and appends a specific file suffix.

If you get an error when running this script and there
are no typeos in the path, make sure your SQL Server
service logon user has appropriate rights to the path.
It does not use your login context to create the backups.

Change the @ variables to fit your needs

***************************
JP Davey
2009
***************************
*/

declare @DatabaseId varchar(100)
declare @SQL varchar(400)
declare @BackupFolder varchar(256)
declare @FileSuffix varchar(256)
declare @GPDatabase tinyint
declare @BackUpGPOnly char(1)

-- Only Back Up Dynamics GP databases: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @BackUpGPOnly = 'Y'

-- The network (UNC) path where the databases will be backed up TO
set @BackupFolder = 'G:\Transfer\' -- must end with a \

-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'


declare DatabaseLoop cursor for
   SELECT
      RTRIM(LTRIM(name)) as name,
      (CASE WHEN name IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
         THEN 1
         ELSE 0 END) as GPDB
   FROM master..sysdatabases
   WHERE name not in ('master','model','msdb','tempdb')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
while (@@fetch_status <> -1)
   begin

   /*Inner Loop for each Database*/
   IF @GPDatabase = 1 OR @BackUpGPOnly = 'N'
      BEGIN
         RAISERROR (@DatabaseId, 0, 1) WITH NOWAIT;
         SET @SQL = 'BACKUP DATABASE [' + @DatabaseId + '] TO DISK = N''' + @BackupFolder
-- ** if you want to create subfolders for each company, you will need to uncomment the following line
-- ** keep in mind that you will have to manually create the folders if they do not already exist
--          + @DatabaseId + '\'
            + @DatabaseId + @FileSuffix
            + '.bak'' WITH NOFORMAT, INIT, NAME = N'''
            + @DatabaseId + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
         print @SQL
         EXEC(@SQL)
         print CHAR(13)+CHAR(10)+'----------------'+CHAR(13)+CHAR(10)
      END

   fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
   end

close DatabaseLoop
deallocate DatabaseLoop

The restore script for restoring the databases and running all the GP scripts to the distination server

/*
This script is compatible with SQL 2005 and 2008

This script creates the script you should open and run on the
new/test server to restore and prepare the databases.
The output from this script is a script
to perform the following tasks:

Restore the databases from the @BackupFolder location
Run the Dex_Req.sql script on the master database
Run the Grant.sql script on all GP databases
Run the sp_changedbowner 'DYNSA' on all GP databases
Run the MassDropUserSchemas.sql script on all GP databases

After running this script on the existing SQL server,
copy/paste the output to the new SQL server and run it there.
Don't include the first line "(xx row(s) affected)".

If you are referencing the backup files from a network share
(UNC path) make sure your SQL Server service logon user has
appropriate rights to the network path.

If it fails on a couple lines, correct the output script on the new
server, commenting out the lines you don't want, and Execute the
script again. Because there are variables declared, you might not be
able to just select a few lines and Execute.
If it fails miserably, you probably don't have your files in
the correct path. Fix the path in this script, Execute, and copy
the script to the new server again.

Change the @ variables to fit your needs

***************************
JP Davey -- Using modified GP scripts
2009
***************************
*/

declare
@BackupFolder varchar(256),
@RestoreDataFileFolder varchar(256),
@RestoreLogFileFolder varchar(256),
@FileSuffix varchar(256),
@DatabaseId varchar(100),
@MDFName varchar(256),
@LDFName varchar(256),
@InDynamics tinyint,
@RestoreGPOnly char(1)

-- The local or network (UNC) path where the databases will be restored FROM
set @BackupFolder = 'D:\Transfer\' -- must end with a \

-- The local path where the Data (.mdf) files will be restored TO
set @RestoreDataFileFolder = 'C:\SQLData\Data\' -- must end with a \

-- The local path where the Log (.ldf) files will be restored TO
-- This may or may not be the same as the @RestoreDataFileFolder
set @RestoreLogFileFolder = 'C:\SQLData\Logs\' -- must end with a \

-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'

-- Restore Dynamics GP databases only: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @RestoreGPOnly = 'Y'

-------------------------------------------------------------------------------
---------- Get a list of database files ---------------------------------------
-------------------------------------------------------------------------------

CREATE TABLE #GenScript_DBInfo
   ( DatabaseName VARCHAR(100),
   LogicalFileName varchar(100),
   LFType smallint)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
' + '''' + '?' + '''' + ' AS DatabaseName,
rtrim(sysfiles.name) AS LogicalFileName,
sysfiles.groupid as LFType
FROM dbo.sysfiles'
INSERT INTO #GenScript_DBInfo
   (DatabaseName,
   LogicalFileName,
   LFType)
EXEC sp_MSForEachDB @command

-------------------------------------------------------------------------------
---------- Restore the databases ----------------------------------------------
-------------------------------------------------------------------------------
print ''
print '-- Restore Databases --'
print ''

declare DatabaseLoop cursor for
   (SELECT
      DB.DatabaseName as DB,
      MFile.LogicalFileName as MDFName,
      LFile.LogicalFileName as LDFName,
      (CASE WHEN DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
         THEN 1
         ELSE 0
       END) as GPDB
   FROM
      #GenScript_DBInfo DB
      LEFT JOIN
      #GenScript_DBInfo MFile
      ON DB.DatabaseName = MFile.DatabaseName
      AND MFile.LFType = 1
      LEFT JOIN
      #GenScript_DBInfo LFile
      ON DB.DatabaseName = LFile.DatabaseName
      AND LFile.LFType = 0
   WHERE
      DB.DatabaseName NOT IN ('master','model','tempdb','msdb')
      AND (@RestoreGPOnly = 'N'
         OR (@RestoreGPOnly = 'Y' AND DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')))
   GROUP BY
      DB.DatabaseName,
      MFile.LogicalFileName,
      LFile.LogicalFileName
   )

declare
@Prefix varchar(3),
@SuffixDat varchar(3),
@SuffixLog varchar(3)

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
while (@@fetch_status <> -1)
   begin
      if @InDynamics = 1
         begin
            set @Prefix = 'GPS'
            set @SuffixDat = 'Dat'
            set @SuffixLog = 'Log'
         end
      else
         begin
            set @Prefix = ''
            set @SuffixDat = ''
            set @SuffixLog = ''
         end
      print 'RESTORE DATABASE [' + @DatabaseId + '] FROM DISK = N''' + @BackupFolder
-- ** if you created subfolders for each company, you will need to uncomment the following line
--       + @DatabaseId + '\'
         + @DatabaseId + @FileSuffix
         + '.bak'' WITH FILE = 1, MOVE N'''
         + @MDFName + ''' TO N''' + @RestoreDataFileFolder
         + @Prefix + @DatabaseId + @SuffixDat + '.mdf'', MOVE N'''
         + @LDFName + ''' TO N''' + @RestoreLogFileFolder
         + @Prefix + @DatabaseId + @SuffixLog + '.ldf'', KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10'
      fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
   end
close DatabaseLoop
deallocate DatabaseLoop

DROP TABLE #GenScript_DBInfo

-------------------------------------------------------------------------------
---------- Grab the logins and passwords --------------------------------------
-------------------------------------------------------------------------------

PRINT ''
PRINT '-- Add Users --'
PRINT ''

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
   @binvalue varbinary(256),
   @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)  BEGIN        DECLARE @tempint int    DECLARE @firstint int        DECLARE @secondint int        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))        SELECT @firstint = FLOOR(@tempint/16)     SELECT @secondint = @tempint - (@firstint*16)     SELECT @charvalue = @charvalue +        SUBSTRING(@hexstring, @firstint+1, 1) +        SUBSTRING(@hexstring, @secondint+1, 1)     SELECT @i = @i + 1  END  SELECT @hexvalue = @charvalue  GO  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL     DROP PROCEDURE sp_help_revlogin  GO  CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL  AS  DECLARE @name sysname  DECLARE @xstatus int  DECLARE @binpwd varbinary (256)  DECLARE @txtpwd sysname  DECLARE @tmpstr varchar (256)  DECLARE @SID_varbinary varbinary(85)  DECLARE @SID_string varchar(256)  IF (@login_name IS NULL)        DECLARE login_curs CURSOR FOR              SELECT sid, name, xstatus, password FROM master..sysxlogins              WHERE srvid IS NULL AND name <> 'sa'
ELSE
   DECLARE login_curs CURSOR FOR
      SELECT sid, name, xstatus, password FROM master..sysxlogins
      WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
   PRINT 'No login(s) found.'
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
      PRINT ''
      SET @tmpstr = '-- Login: ' + @name
      PRINT @tmpstr
      IF (@xstatus & 4) = 4
         BEGIN -- NT authenticated account/group
            IF (@xstatus & 1) = 1
               BEGIN -- NT login is denied access
                  SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
                  PRINT @tmpstr
               END
            ELSE BEGIN -- NT login has access
               SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
               PRINT @tmpstr
            END
         END
      ELSE BEGIN -- SQL Server authentication
         IF (@binpwd IS NOT NULL)
            BEGIN -- Non-null password
               EXEC sp_hexadecimal @binpwd, @txtpwd OUT
               IF (@xstatus & 2048) = 2048
                  SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
               ELSE
                  SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
               PRINT @tmpstr
               EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
               SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
                  + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
            END
         ELSE BEGIN
            -- Null password
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
               + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
         END
         IF (@xstatus & 2048) = 2048
            -- login upgraded from 6.5
            SET @tmpstr = @tmpstr + '''skip_encryption_old'''
         ELSE
            SET @tmpstr = @tmpstr + '''skip_encryption'''
            PRINT @tmpstr
         END
      END
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
GO
create procedure seeMigrateSQLLogins @login_name sysname = NULL
as
declare
   @name char(50),
   @binpwd varbinary (256),
   @txtpwd sysname,
   @tmpstr varchar (256),
   @SID_varbinary varbinary(85),
   @SID_string varchar(256),
   @Is_Policy bit,
   @Is_Exp bit,
   @type char(1),
   @Pol char(3),
   @Exp char(3)
set nocount on
create table #logins (
   [name] nvarchar(128) NOT NULL,
   [sid] varbinary(85) NOT NULL,
   [type] char(1) NOT NULL,
   [is_policy_checked] bit default 0,
   [is_expiration_checked] bit default 0,
   [password_hash] varbinary(256) )
insert #logins (name, sid, type)
select name, sid, type from sys.server_principals where
   (type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITY\SYSTEM'
update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
   from #logins a, sys.sql_Logins b
   where a.sid = b.sid
set nocount off
IF (@login_name IS NULL) --Not a single user, get the list
   DECLARE seelogin_curs CURSOR FOR
      SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
      WHERE name <> 'sa'
ELSE
   DECLARE seelogin_curs CURSOR FOR
      SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
      WHERE name = @login_name
OPEN seelogin_curs
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
IF (@@fetch_status = -1)
BEGIN
   PRINT 'No login(s) found.'
   CLOSE seelogin_curs
   DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
   IF @type = 'S'
      BEGIN
         PRINT '/* SQL Login ******************/'
         EXEC sp_hexadecimal @binpwd, @txtpwd OUT
         EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
         IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END
         IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END
         SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
         PRINT @tmpstr
         PRINT ''
      END
   Else
      BEGIN
         PRINT '/* SQL Login ******************/'
         EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
         SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
         PRINT @tmpstr
         PRINT ''
      END
      FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
   END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
drop table #logins
GO

declare @version2005 char(5)
declare @version2008 char(5)

--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)

if @version2005 = '9.00'
   Begin
      exec seeMigrateSQLLogins
   End
Else if @version2008 = '10.0'
   Begin
      exec seeMigrateSQLLogins
   End
Else
   begin
      exec sp_help_revlogin
   End

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
GO

-------------------------------------------------------------------------------
---------- Dex_Req.sql --------------------------------------------------------
-------------------------------------------------------------------------------
PRINT ''
PRINT '-- Dex_Req.sql --'
PRINT ''
print 'use tempdb'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_LOCK'') and type = ''U'')'
print 'begin'
print ' drop table DEX_LOCK'
print 'end'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_SESSION'') and type = ''U'')'
print 'begin'
print ' drop table DEX_SESSION'
print 'end'
print 'go'
print 'use master'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.smDEX_Build_Locks'') and type = ''P'')'
print 'begin'
print ' drop procedure smDEX_Build_Locks'
print 'end'
print 'go'
print 'create procedure smDEX_Build_Locks'
print 'as'
print 'exec (''create table tempdb..DEX_LOCK (session_id int, row_id int, table_path_name char(100))'')'
print 'exec (''create unique index PK_DEX_LOCK on tempdb..DEX_LOCK(row_id,table_path_name)'')'
print 'exec (''create table tempdb..DEX_SESSION (session_id int identity, sqlsvr_spid smallint)'')'
print 'exec (''create unique index PK_DEX_SESSION on tempdb..DEX_SESSION(session_id)'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_LOCK to public'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_SESSION to public'')'
print 'return'
print 'go'
print 'sp_procoption ''smDEX_Build_Locks'',''startup'',''true'''
print 'go'
print 'smDEX_Build_Locks'
print 'go'

-------------------------------------------------------------------------------
---------- Grant.sql and sp_changedbowner 'DYNSA' -----------------------------
-------------------------------------------------------------------------------

PRINT ''
PRINT '-- Grant.sql, sp_changedbowner, and remove user schemas --'
PRINT ''

declare @DatabaseId varchar(8)

declare DatabaseLoop cursor for
   select name from master..sysdatabases where name in
      (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId

print 'declare @cStatement varchar(255)'
print 'declare @UserId sysname'

while (@@fetch_status <> -1)
   begin
      print ''
      print '-----------------------------------------'
      print 'print ''Security for ' + @DatabaseId + ''''
      print 'USE [' + @DatabaseId + ']'
      print ''
      print 'declare G_cursor CURSOR for select ''grant select,update,insert,delete on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
      print ' where (type = ''U'' or type = ''V'') and uid = 1'
      print 'set nocount on'
      print 'OPEN G_cursor'
      print 'FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'WHILE (@@FETCH_STATUS <> -1)'
      print 'begin'
      print ' EXEC (@cStatement)'
      print ' FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'end'
      print 'DEALLOCATE G_cursor'
      print 'declare G_cursor CURSOR for select ''grant execute on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
      print ' where type = ''P'''
      print 'set nocount on'
      print 'OPEN G_cursor'
      print 'FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'WHILE (@@FETCH_STATUS <> -1)'
      print 'begin'
      print ' EXEC (@cStatement)'
      print ' FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'end'
      print 'DEALLOCATE G_cursor'
      -- Change Owner
      print ''
      print 'exec sp_changedbowner ''DYNSA'''
      -- Remove user schemas
      print ''
      print 'declare UserIDLoop cursor for select USERID from DYNAMICS..SY01400'
      print 'open UserIDLoop'
      print 'fetch next from UserIDLoop into @UserId'
      print 'while (@@fetch_status <> -1)'
      print ' begin'
      print ' IF EXISTS (SELECT * FROM sys.schemas WHERE name = @UserId)'
      print ' exec(''DROP SCHEMA '' + @UserId)'
      print ' fetch next from UserIDLoop into @UserId'
      print ' end'
      print 'close UserIDLoop'
      print 'deallocate UserIDLoop'

   fetch next from DatabaseLoop into @DatabaseId
   end
close DatabaseLoop
deallocate DatabaseLoop

Needless to say, this is a "use at your own risk" sort of thing.