Old Live Database to Dev Database

Lee_Fry · October 15, 2025, 11:11am

We have a development server that is running 2025.1 with its own database etc, I would like to restore our live database (2023.1) over our development one to refresh all the data with a more updated live version as the dev database is over a year out of date with that.

I’ve tried the usual route of restoring live over dev, and then upgrading the database via admin console (as if it was a fresh upgrade) but this failed sadly.

Is there anything I’m missing on this process? I would expect it to work as if it was a fresh upgrade.

This is the log from the database upgrade:

Log File Location: C:\Program Files (x86)\Common Files\Epicor Software\Database Manager Extensions\5.0.100\DB Migration\ERPDev_Results_10_15_2025 12_08_20.txt

Starting Preparation
   - PatchCommonDB Process 
Preparation Complete: 0.0156075 seconds

Warning	No Task	No update file found	The CommonDB version 5.0.100.12 is up to date.  There is no need to run any of the patches.
Starting Execution Phase...
   - PatchCommonDB Process 
Execute Phase Complete: 0.0025523 seconds 

Starting Cleanup Phase...
   - PatchCommonDB Process 
Cleanup Phase: 0.0005083 seconds

Starting Preparation
   - Pre-Migration 
Preparation Complete: 0.0027666 seconds

Starting Execution Phase...
   - Pre-Migration 
Execute Phase Complete: 1.7426788 seconds 

Starting Cleanup Phase...
   - Pre-Migration 
Cleanup Phase: 0.0005446 seconds

Starting Preparation
   - Upgrade Process  ICE4.3.200.0
Preparation Complete: 0.0030025 seconds

Starting Execution Phase...
   - Upgrade Process  ICE4.3.200.0
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreateActionMaster.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreateActionPlanDtl.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreateActionPlanHdr.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreateActionPlanMasterDtl.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreateActionPlanMasterHdr.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreatePreviewFeature.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreatePreviewFeatureCompany.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: CreatePreviewFeatureUser.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: DropCustomReportStore_backup.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyAPITrace.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyCustomReportHistory.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyCustomReportStore.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyDmsStorageType.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyDocType.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyGlobalSetting.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyIceColumnView.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyICEVer.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyIMSysRptLst.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyQueryTable.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifySolutionHistory.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifySolutionInstall.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifySysUserFile.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: ModifyZBODef.sql
Information	Upgrade Process	Upgrade Process  ICE4.3.200.0~	Executing: UpdateSysConfig.sql
Error	Upgrade Process	ModifySysUserFile.sql	Column 'AllowAPIv1' in table 'SysUserFile' is invalid for creating a default constraint.
Could not create constraint or index. See previous errors.
alter table Ice.SysUserFile
add constraint DF_sys
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Epicor.MigrationModel.DBMigrationTask.RunCommand(Int32 totalObjects, SqlConnection cnx, Int32& lastPct, Int32& itemsProcessed, String sqlCommandText, String scriptFileName, SqlTransaction trans)
Error	Upgrade Process	ModifySysUserFile.sql	Column 'AllowAPIv1' in table 'IMSysUserFile' is invalid for creating a default constraint.
Could not create constraint or index. See previous errors.
alter table IM.IMSysUserFile
add constraint DF_IM
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Epicor.MigrationModel.DBMigrationTask.RunCommand(Int32 totalObjects, SqlConnection cnx, Int32& lastPct, Int32& itemsProcessed, String sqlCommandText, String scriptFileName, SqlTransaction trans)
Execute Phase Complete: 2.8757694 seconds 

Starting Cleanup Phase...
   - Upgrade Process  ICE4.3.200.0
Cleanup Phase: 0.000761 seconds

DB Migration process is cancelled by the user.
The total execution time is 0.0792 minutes.

Hally · October 15, 2025, 11:45am

Are you sure the user account that you are using to do the upgrade has the correct access to the database?

Lee_Fry · October 15, 2025, 12:09pm

Yeah, deffo using the right account details.

Lee_Fry · October 15, 2025, 12:54pm

Database versions are both the same (2019) to and with the same compatibility settings (150)

gpayne · October 15, 2025, 4:31pm

I am confused by your version and your profile, but the issue is with the ModifySysUserFile.sql script and the AllowAPIv1 field and this error Column ‘AllowAPIv1’ in table ‘IMSysUserFile’ is invalid for creating a default constraint. Also in SysUserFile.

I would check that the column exists already and possibly just comment out the commands in this file if it it already a constraint.

Lee_Fry · October 16, 2025, 8:31am

Looks like I got it all working in the end, for anyone that encounters this issue in the future… I had to delete a previous migration task file from:

C:\Program Files (x86)\Common Files\Epicor Software\Database Manager Extensions\5.0.100\DB Migration\MigrationTasks\

As it seems to have been loading settings from this that had different tasks id required etc, so was running stuff that was not needed.

Thanks
Lee