Schedule restore of live company to test company Dyanamics GP

To keep a fresh copy of our test database always available, I scheduled a restore from the live database into the test company database to occur once a week.
[Please also checkout my updated related post Automated restore of Live Company to Test Company in GP2013 CHANGES 21 Jan 2015]

This is how I do that:

Credit to the stack overflow question for how to find latest backup

Sql to get most recent full backup file for a specific database in sql server

This Microsoft Document shows official GP guide for restoring a live db into test. CHECK THIS AS YOUR INSTALL MAY USE MODULES WE DONT- THAT WILL REQUIRE EXTRA STEPS!!

Set up a test company that has a copy of live company data by using SQL Server

Create a SQL Job and schedule it to run once a week (or whatever you frequency you choose). To this job add the following steps.

Step 1 Restore from Live company into Test company

This step should be set to run from the Master database when configuring it in SQL agent (updated for GP2013 21 Jan 2015).

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = '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, '+rtrim(DBNAME)+'.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'
  from SY00100
  exec (@Statement)
end
else begin
  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
    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
end

Step 2 Restore the company ID or database id name value

There are references in GP to the company ID in the company database. The company ID is held by the DYNAMICS database. To correct the freshly restored database so it thinks it is the test company introduce the following step. Configure this to run from the test company database when working with it in SQL agent.

/******************************************************************************/

/*    Description:    */

/*    Updates any table that contains a company ID or database name value    */

/*    with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table    */

/*    */

/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
   create table [##updatedTables] ([tableName] char(100))
 truncate table ##updatedTables
 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, INFORMATION_SCHEMA.TABLES c
 where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
   and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE' 
and a.TABLE_NAME <> 'UPR70501' and a.TABLE_NAME <> 'MPOSMAIN'
   set nocount on
 OPEN G_cursor
 FETCH NEXT FROM G_cursor INTO @cStatement
 WHILE (@@FETCH_STATUS <> -1)
 begin
    insert ##updatedTables select
 substring(@cStatement,8,patindex('%set%',@cStatement)-9)
    Exec (@cStatement)
   FETCH NEXT FROM G_cursor INTO @cStatement
 end
 DEALLOCATE G_cursor
 select [tableName] as 'Tables that were Updated' from ##updatedTables

Step 3 Change the current database owner

Configure to run against the test company.

    sp_changedbowner 'DYNSA'

Step 4 Fix the Next Note ID

The next note ID is held against each company in the DYNAMICS database. This needs changing for the test company to represent the freshly restored data. Run the following to correct the IDS. Configure to run against the test company.

/*
** FindMaxNoteIndex.SQL
**
** Purpose:
**
** Find the max value of NOTEINDX from all tables.
** 
** This script must be run against the company in which the notes are incorrect.
** it will automatically update your SY01500 for you to the correct next note index.
**
** Modified by MRO
*/ 

if exists (select * from tempdb..sysobjects where name = '##GPSMaxNote')
 drop table dbo.##GPSMaxNote 

set nocount on
create table ##GPSMaxNote (MaxNoteIndex numeric(19,5) null)
go 


declare @cStatement varchar(255) /* Value from the t_cursor */
declare @noteidx numeric(19,5)
declare @database as varchar(5)
set @database = cast(db_name() as varchar(5)) 

/* Get the tables that have a column name of NOTEINDX. */
declare T_cursor cursor for
 select 'declare @NoteIndex numeric(19,5) select @NoteIndex = max(NOTEINDX) from ' + o.name + ' insert ##GPSMaxNote values(@NoteIndex)'
     from sysobjects o, syscolumns c
     where o.id = c.id
        and o.type = 'U'
        and c.name = 'NOTEINDX' 

/* Ok, we have the list of tables.  Now get the max value of NOTEINDX from each table. */

open T_cursor
fetch next from T_cursor into @cStatement
while (@@fetch_status <> -1)
   begin
   exec (@cStatement)
   fetch next from T_cursor into @cStatement
   end
deallocate T_cursor 

select 'Max Note Index:', max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null 
use DYNAMICS
set @noteidx = (select max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null) 

update SY01500 set NOTEINDX = (@noteidx + 1.0) where INTERID=@database
set nocount off