This will be something even a novice DBA would know about, but hopefully it might be helpful for a Dynamics AX developer who isn't too familiar with SQL - but wants to learn.
I once had to help someone get back a couple of columns that were lost due to a flawed client cache (apparently). This may also easily happen if someone accepts a database synchronization which creates data loss of specific columns.
The solution is quite easy. A DBA or developer with some SQL Server experience can prepare a restored database sitting on the same SQL Server Instance, or create a Linked Server for the purpose of this restore.
When the database is ready, make sure to keep users away while you restore the data. It should be a matter of minutes. I restored 3 columns in around 800 000 rows in just a few minutes. Keeping users away is just a precaution. SQL Server will by itself make sure the entire update either fails or succeeds.
Here is an example SQL query to restore the columns:
BEGIN TRANSACTION RestoreColumnData WITH MARK N'Restoring Column Data'; GO UPDATE SalesTableInTarget SET SalesTableInTarget.CustomColumn1 = SalesTableInSource.CustomColumn1 , SalesTableInTarget.CustomColumn2 = SalesTableInSource.CustomColumn2 , SalesTableInTarget.CustomColumn3 = SalesTableInSource.CustomColumn3 FROM DynamicsAXTarget.dbo.SALESTABLE AS SalesTableInTarget INNER JOIN DynamicsAXSource.dbo.SALESTABLE AS SalesTableInSource ON SalesTableInSource.RECID = SalesTableInTarget.RECID GO COMMIT TRANSACTION RestoreColumnData GO
The query simply joins the target table and the source table based on the RecId,which should be unique. I chose to mark the update in a custom transaction.
You can find multiple examples of similar queries on the web, but sometimes it helps to put these things in our AX context.
Hope this helps someone.