Dynamics GP Restore Live to Test Company Script Generator (KB 871973)

-- 1. The test company TEST already exists, created in GP Utilities with its own company ID.
-- 2. All users are out of GP; Management Reporter services are stopped if MR is used.
-- 3. If Analytical Accounting is active in TWO, activate AA in TEST before restoring.
-- 4. Run every statement below on the test side only. Never run block 4 or later in TWO.

BACKUP DATABASE [TWO]
TO DISK = N'D:\SQLBackup\TWO_copy.bak'
WITH COPY_ONLY, INIT, COMPRESSION, STATS = 10;
-- remove COMPRESSION if your SQL Server edition does not support it

RESTORE FILELISTONLY FROM DISK = N'D:\SQLBackup\TWO_copy.bak';
-- LogicalName column: use these values in the MOVE clauses below; do not rename them

USE master;
ALTER DATABASE [TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [TEST]
FROM DISK = N'D:\SQLBackup\TWO_copy.bak'
WITH MOVE N'GPSTWODat.mdf' TO N'D:\SQLData\GPSTESTDat.mdf',
     MOVE N'GPSTWOLog.ldf' TO N'L:\SQLLogs\GPSTESTLog.ldf',
     REPLACE, STATS = 10;

ALTER DATABASE [TEST] SET MULTI_USER;
ALTER DATABASE [TEST] SET RECOVERY SIMPLE;  -- optional for test

USE [TEST];
GO
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
  then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
  else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    and rtrim(a.TABLE_NAME)+'-'+rtrim(a.COLUMN_NAME) <> 'SY00100-DBNAME'
  order by a.TABLE_NAME
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
close G_cursor
DEALLOCATE G_cursor
set nocount off
GO
-- if a duplicate key error names PKxxxxx, fix INTERID/COMPANYID in table xxxxx manually

UPDATE TAST0130 SET COMPANYCODE_I = 'TEST';
-- or re-enter Tools > Setup > Human Resources > Attendance > Setup in the test company and save

USE [TEST];
EXEC sp_changedbowner 'DYNSA';
-- equivalent on newer SQL Server versions:
-- ALTER AUTHORIZATION ON DATABASE::[TEST] TO DYNSA;

-- Analytical Accounting: run the AAG00102 next-number fix from the Microsoft-linked AA test company article before entering transactions.
-- Fixed Assets: re-enter Fixed Assets Company Setup in the test company.
-- SSRS / Excel report drilldowns: run GP Database Maintenance, Functions and Stored Procedures plus Views, for all databases.
-- Workflow and email: change approver emails and SMTP settings so test does not mail real users.
-- Integrations, eConnect jobs, scheduled SmartConnect or SQL Agent jobs: point them at TEST or disable them.
-- Optional: rename the company display name in GP so users can see they are in test.