Recover lost columns using SQL

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
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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.