Monday, April 1, 2013

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.

No comments:

Post a Comment