Dynamics Community Forum Thread Details

Here are the MS instructions on this, and steps to properly perform restores to test companies:

Drop this into SQL Management Studio. Instructions are in the comments:

Here is also a link to my GitHub repository code for this if you want to see a nicer view of it. https://gist.github.com/6232244

/*

To Copy live database to test company database

If you are using SQL Server Management Studio, follow these steps: 
l.                     Click Start, and then click Programs. 
m.    Point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Connect to Server window opens. 
n.	In the Server name box, type the name of the instance of SQL Server. 
o.	In the Authentication list, click SQL Authentication. 
p.	In the User name box, type sa. 
q.	In the Password box, type the password for the sa user, and then click Connect. 
r.	In the Object Explorer section, expand Databases. 
s.	Right-click the test company database, point to Tasks, point to Restore, and then click Database. 
t.	In the Source for Restore area, click From Device, and then click the ellipsis button. 
u.	In the Backup Location area, click Add. 
v.	Find the location where saved the backup file, select LIVE.bak file, and then click OK. 
w.	Click OK. You return to the Restore Database window. 
x.	In the Select the Backup Sets to Restore section, click the backup file that you want to restore. 
y.	In the Select a Page area, click Options. 
z.	In the Restore Database Files as area, you will need to change the location of these two files from the Live database to the test database's .mdf and .ldf files. By default, these will be selected on the Live database's .mdf and .ldf files.

run the script below to fix the user mappings to the company database.

Note The logical file name reflects the name of the live database. Do not change the logical file name. 
aa.	To change these locations, click on the Ellipse (…) next to the file location field. 
bb.	Navigate to the path that you noted in step 1, where the test database was created. 
cc.	Highlight the respective .mdf file, and then click OK. 
dd.	Repeat steps p through r, select the .ldf file, and then click OK. 
ee.	Click to select the Overwrite existing database check box. 
ff.	Click OK to return to the Restore Database window
If you are using Microsoft Dynamics GP 10.0 or later, follow these steps to copy the security permissions from the live company to the test company: 
gg.               Log on to Microsoft Dynamics GP as the sa user. 
hh.	Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click User Access. 
ii.	Select an appropriate user, and then make sure that the check box for the new test company is selected to indicate that access is granted. 

Note If you receive an error message when you click to select a company, delete the user from the Users folder under the new test database in SQL Server Management Studio or in Enterprise Manager. 
jj.	Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click User Security. 
kk.	In the Security Task Setup window, select the user who you want to have access to the test company. 
ll.	In the Company list, click the live company. 
mm.	Click Copy, click to select the check box that is next to the test company, and then click OK. 




*/

USE DYNAMICS

   
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

go


sp_changedbowner 'DYNSA'

go

Community site session details

invalid object name SY01400

Helpful resources

News and Announcements

Quick Links

Jainam Kothari – Community Spotlight

Congratulations to the May Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates