You restored a live database to the test company and found that your data is missing in the existing live company.
Reason - the database name in the company maintenance window of SL is incorrect.
Have all users exit the system and manually update the Company table in the system database to reflect the correct application database name.
Thursday, 27 April 2017
Dynamics SL Backup and Restore Company
1. Create additional empty database in Database Maintenance
2. Create company in SL. Go to System Manager>> Maintenance>> Company Maintenance.
Create new company ID, enter database name and other details.
3. Restore copy of live application database to test application database
4. In the domain table in the Microsoft Dynamics SL system database, update the application database name. Execute against the system database
UPDATE COMPANY SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'
UPDATE DOMAIN SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'
-Note Replace XXXXXX with the new name of the application database, and replace YYYYYY with the old database name.
5. Update database views in Database Maintenance.
-Database Maintenance.
-Connect to SQL Server instance
-In the System Database Name list, click the restored Microsoft Dynamics SL system database.
-Under Databases, click the restored Microsoft Dynamics SL application database.
-Click Update Views.
-At the bottom of the Database Maintenance (98.290.00) window, confirm that the progress bar has finished.
-Click Close.
-Log on to Microsoft Dynamics SL by using the appropriate SQL Server name and the appropriate Microsoft Dynamics SL system database name.
6. If errors are encountered. Run the triggers script in sql.
Triggers script:
-- Drop all windows authentication triggers.
-- If using windows authentication, it will also recreate the triggers
-- and cleans up any stray vs_acctsub or vs_acctxref records
--
-- 1 - Make a good database backup
-- 2 - Run as is against your SL System database
--
-- last updated: 5/13/2008
-- Step 1: Drop all ACCTSUB and ACCTXREF triggers
declare @triggername as char(100)
declare @execString as char(200)
DECLARE trigger_cursor CURSOR FOR
select name from sysobjects where type='TR' and (
name like 'sDeleteAcctSub_%' or
name like 'sInsertAcctSub_%' or
name like 'sUpdateAcctSub_%' or
name like 'sDeleteAcctXref_%' or
name like 'sInsertAcctXref_%' or
name like 'sUpdateAcctXref_%')
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @triggername
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString = 'drop trigger ' + @triggername
print @execString
exec (@execString)
print 'Done'
FETCH NEXT FROM trigger_cursor INTO @triggername
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor
-- only do step 2 and 3 if windows auth
if (select top 1 text from syscomments where ID in (select ID from sysobjects where name='getauthenticationtype' and type='P'))
like '%Windows%'
begin
-- Step 2: Recreate a new set of 6 triggers for each app database listed in the company table
declare @dbname as char(100)
declare @execString2 as char(1000)
DECLARE db_cursor CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString2 = 'CREATE TRIGGER sDeleteAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_AcctSub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_AcctSub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctsub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
+' Insert into '+rtrim(@dbname)+'..vs_acctsub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sDeleteAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
+' Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Step 3: Cleanup any stray vs_acctxref or vs_acctsub records
declare @dbName3 as char(85)
declare @execString3 as char(200)
DECLARE db_cursor3 CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor3
FETCH NEXT FROM db_cursor3 INTO @dbName3
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctxref a left join acctxref b on a.acct=b.acct and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctsub a left join acctsub b on a.acct=b.acct and a.sub=b.sub and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
FETCH NEXT FROM db_cursor3 INTO @dbName3
END
CLOSE db_cursor3
DEALLOCATE db_cursor3
END
-- END
2. Create company in SL. Go to System Manager>> Maintenance>> Company Maintenance.
Create new company ID, enter database name and other details.
3. Restore copy of live application database to test application database
4. In the domain table in the Microsoft Dynamics SL system database, update the application database name. Execute against the system database
UPDATE COMPANY SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'
UPDATE DOMAIN SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'
-Note Replace XXXXXX with the new name of the application database, and replace YYYYYY with the old database name.
5. Update database views in Database Maintenance.
-Database Maintenance.
-Connect to SQL Server instance
-In the System Database Name list, click the restored Microsoft Dynamics SL system database.
-Under Databases, click the restored Microsoft Dynamics SL application database.
-Click Update Views.
-At the bottom of the Database Maintenance (98.290.00) window, confirm that the progress bar has finished.
-Click Close.
-Log on to Microsoft Dynamics SL by using the appropriate SQL Server name and the appropriate Microsoft Dynamics SL system database name.
6. If errors are encountered. Run the triggers script in sql.
Triggers script:
-- Drop all windows authentication triggers.
-- If using windows authentication, it will also recreate the triggers
-- and cleans up any stray vs_acctsub or vs_acctxref records
--
-- 1 - Make a good database backup
-- 2 - Run as is against your SL System database
--
-- last updated: 5/13/2008
-- Step 1: Drop all ACCTSUB and ACCTXREF triggers
declare @triggername as char(100)
declare @execString as char(200)
DECLARE trigger_cursor CURSOR FOR
select name from sysobjects where type='TR' and (
name like 'sDeleteAcctSub_%' or
name like 'sInsertAcctSub_%' or
name like 'sUpdateAcctSub_%' or
name like 'sDeleteAcctXref_%' or
name like 'sInsertAcctXref_%' or
name like 'sUpdateAcctXref_%')
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @triggername
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString = 'drop trigger ' + @triggername
print @execString
exec (@execString)
print 'Done'
FETCH NEXT FROM trigger_cursor INTO @triggername
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor
-- only do step 2 and 3 if windows auth
if (select top 1 text from syscomments where ID in (select ID from sysobjects where name='getauthenticationtype' and type='P'))
like '%Windows%'
begin
-- Step 2: Recreate a new set of 6 triggers for each app database listed in the company table
declare @dbname as char(100)
declare @execString2 as char(1000)
DECLARE db_cursor CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString2 = 'CREATE TRIGGER sDeleteAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_AcctSub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_AcctSub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctsub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
+' Insert into '+rtrim(@dbname)+'..vs_acctsub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sDeleteAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sInsertAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
set @execString2 = 'CREATE TRIGGER sUpdateAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
+' Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Step 3: Cleanup any stray vs_acctxref or vs_acctsub records
declare @dbName3 as char(85)
declare @execString3 as char(200)
DECLARE db_cursor3 CURSOR FOR
select distinct databasename from company where databasename<>''
OPEN db_cursor3
FETCH NEXT FROM db_cursor3 INTO @dbName3
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctxref a left join acctxref b on a.acct=b.acct and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctsub a left join acctsub b on a.acct=b.acct and a.sub=b.sub and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
FETCH NEXT FROM db_cursor3 INTO @dbName3
END
CLOSE db_cursor3
DEALLOCATE db_cursor3
END
-- END
Subscribe to:
Posts (Atom)