Sage 100

I had a client with a virus with Sage 100 2016 Premium. They had no files to restore for the MAS directory. Full loss of all data and backup. I installed a new instance and was able to restore their SQL from the old instance. All seemed too good until... I determined the restored datatables do not appear to have UDFs. The UDFs appear in Custom Office but no columns appear in SQL when I look at the SSMS. Any magic tricks? I already know they are missing (Custom Screens, all Paperless PDFs, We had to restore forms from April).

I don't work in premium a lot but if you can see the UDFs in custom office it seems logical that if you went into each table and clicked on UPDATE button it would add them to the SQL tables.

If you run the ""update"" command from inside the UDF maintenance I think they will be added.

So, I will get columns but not data?

Is there a way to update all tables at the same time?

unfortunately not... you will like need to import via VI or via a SSIS import.

About that... They cleared the server and formatted everything. I have nothing to go back to other than the SQL backups.

If the data was restored, why would SQL not have the additional columns?

If there is not a utility to update all UDFs, is there a way to determine all tables that have UDFs? I do not see a report.

The only way I know is to run the Report at the bottom of the UDF Maintenance screen ( Right Printer button)

@LarryBradford, if the data was restored from a SQL backup and if the UDF columns existed in the SQL backup, those columns and their values would also be restored. If the columns are now missing, there are two possibilities. One is that those UDF columns did not exist when the backup was made. The other, more favorable option, is that a timing issue causes Sage 100 to redefine (update physical) those tables in SQL before the UDFs were in place. If it is the latter, then you should be able to use the Update button to get the UDFs back in the SQL schema then restore the SQL backups. I believe that I would restore those SQL backups to a different database name then check to see if the UDF columns exist there. If they do not, then all you can do is redefine them and have no values in them.

Agree with @PhilipWhirley - the restore without a doubt would have the udf columns and data at time of the backup. If the backup was prior to when these fields were added then they would not be there.

OK. This is what I have found. I created a new company Z02. I restored 300 to Z02 using the SQL restore. Even though the live 300 (which is a 7 day old backup restored on Thursday) does not have those columns, the Z02 company does. I then ran the Sage conversion (PU2 was installed) and the DSD APMC conversion utility and the additional columns are still there. The only thought is that the restore looked at the schema and restored without those columns. The UDFs were definitely there from the backup a week ago. What we don't know is what and where they were stripped out from all tables.

The sql restore would have nothing to do with Sage 100 as to whether the columns are there are not. If you go into SQL studio mgr and go to like GL_DetailPosting or SalesOrderHeader table - look at how new the data is there from the restore. Now I have seen in some cases where the UDFs were not hitting all the companies - so if I added for example a vendor master UDF and I had 100 Sage 100 Companies - maybe the first 30 would have the udf and the remaining company databases would not. Even clicking update would not do the rest and no errors would occur. So that might be a possibility too but this is a separate issue.

I did try my first attempt by installing a raw install and then restoring 1 company and MAS_SYSTEM. That did not work so the databases were deleted, the software uninstalled, the 1 company and MAS_System restored before then installing Sage Again.

I think the invalid list may have been related to the UDFs not being in the table.

What does this do? I have determined they had this error and ran the following... Invalid IOLIST specification Have all users exit Sage 100 ERP From Sage 100 Desktop: 1. Open File, Run 2. Enter SYZCON in the Program field 3. At the prompt, enter the following command: O=NEW(""SY_ACTIVATION_BUS"",%SYS_SS) 4. Click the Enter key 5. At the prompt enter the following command: PRINT O'ACTIVATE(""A/R"",""xxx"") xxx = Company Code (ex. ABC) 6. Click the Enter key - 1 is returned if the command was successful - 0 is returned if the command failed

This is a sobering reminder that all of your clients need a point in time based disaster recovery solution. Not a backup solution based on 30 year old designs. It's really like insurance. Nobody likes paying $1,000+ /year for homeowners insurance that there is a 95% chance they will never use, but when the house burns down and $400,000 in equity is gone in minutes, suddenly its the cheapest thing you ever bought.