Simple way to recover lost columns

Easter vacation is almost over and I felt like posting a new blog entry.

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.