Eventually there will come a time when it is necessary to restore data to Dynamics AX. In my case it happened when an X++ job we used as part of a data import process changed some data we did not intend to change. Luckily in our case no data was deleted and only a single column was changed incorrectly. Specifically the X++ changed the InventDimId on all BOM lines for the company in question. It was not noticed until the next day.

The heart of Dynamics AX is a Microsoft SQL server, which itself has robust and varied SQL restore options. Most obvious is the full database restore, however in my situation this would have undone a nights worth of data collection from our shopfloor collection system, which was deemed unacceptable. A more discrete option was required.

Since we test all data imports tasks in a pre-production environment we had a duplicate of the issue in question. Even though the data migration had been tested in preproduction this issue was not found however it did provide a good environment to test our fix.

Here is the basic process that was used to restore the data:
  1. Restore a full copy of the data to a new database in preproduction
  2. Use the sql Update command to restore a single column from the restored data to the preproduction environment
  3. Use a simple SQL select to confirm the data had been restored
  4. Use the Dynamics AX consistency Check on the affected table
  5. Link the PreProduction SQL server to the Production Server
  6. Run the SQL Update command to restore data from the linked server to the production server
  7. Use a simple SQL select to confirm the data had been restored
  8. Use the Dynamics AX consistency Check on the affected table


The process above worked quickly and effectively. Below is the detailed steps taken.

1)Restore a full copy of the data to a new database in preproduction

This step is a straight forward SQL restore. In our case I used transaction logs to create a copy of th production database on our pre-production SQL server. We do not have any special software backing up our SQL database, just SQL agent jobs. Since this was a one-time restore I used the GUI to generate a script like below:

USE [master]
RESTORE DATABASE [AX2012_RefreshSource] FROM DISK N'\\fssav01\sql\BackUps\AXProd\Full\TCI_AX2012R2_PROD\TCI_AX2012R2_PROD_backup_2016_01_17_000001_8207095.bak' WITH FILE = 1,
MOVE N'TCI_CA_AX2012_STD'TON'F:\Restore\AX2012_RefreshSource.mdf',
MOVEN'TCI_CA_AX2012_STD_log'TON'G:\Logs\AX2012_RefreshSource_log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS= 5
RESTORE LOG [AX2012_RefreshSource] FROM DISK = N'\\fssav01\sql\Back-Ups\AXProd\Trans\TCI_AX2012R2_PROD\TCI_AX2012R2_PROD_backup_2016_01_17_010002_3196356.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AX2012_RefreshSource] FROM DISK = N'\\fssav01\sql\Back-Ups\AXProd\Trans\TCI_AX2012R2_PROD\TCI_AX2012R2_PROD_backup_2016_01_17_011501_3284227.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AX2012_RefreshSource] FROM DISK = N'\\fssav01\sql\Back-Ups\AXProd\Trans\TCI_AX2012R2_PROD\TCI_AX2012R2_PROD_backup_2016_01_17_013001_6184715.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AX2012_RefreshSource] FROM DISK = N'\\fssav01\sql\Back-Ups\AXProd\Trans\TCI_AX2012R2_PROD\TCI_AX2012R2_PROD_backup_2016_01_17_014501_6585187.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
ALTER DATABASE [AX2012_RefreshSource] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [AX2012_RefreshSource]
GO
ALTER DATABASE [AX2012_RefreshSource]
MODIFY FILE (NAME =N'TCI_CA_AX2012_STD', NEWNAME= N'AX2012_RefreshSource')
GO
ALTER DATABASE [AX2012_RefreshSource]
MODIFY FILE (NAME =N'TCI_CA_AX2012_STD_log', NEWNAME= N'AX2012_RefreshSource_log')
GO

DECLARE @sql NVARCHAR (MAX) = ''
SELECT @sql = @sql + N'DBCC SHRINKFILE('+CAST (file_id AS NVARCHAR )+N', 256);'
FROM sys .database_files
WHERE type = 1
select @SQL
EXEC (@SQL )

2)Use the sql Update command to restore a single column from the restored data to the preproduction environment

In my situation the update command was appropriate. If you had not only changed data but also deleted data then the Merge command may be more efficient.

update T

set T. INVENTDIMID = S .inventdimID

from TCI_AX2012_UAT. dbo.BOM as T

join AX2012_RefreshSource. dbo.bom as S
on T. RECID = s .RECID

where T. DATAAREAID = 'TIND' --Limit to company with Issue

The update is really very simple, since no records were deleted a simple match on RecID will provide reliable restoration. There is always a chance the data has been changed since the error, to something different then contained in the back-up. In my situation this risk was deemed acceptable.

3)Use a simple SQL select to confirm the data had been restored

select
S .INVENTDIMID as desired,
t .INVENTDIMID as C
from TCI_AX2012_UAT. dbo.BOM as T

join AX2012_RefreshSource. dbo.bom as S
on T. RECID = s .RECID

where T. DATAAREAID = 'TIND'

A similar statement could be run before beginning to determine the scope of the issue.

4)Use the Dynamics AX consistency Check on the affected table

Not much to say about this, it is standard AX functionality.

5) Link the PreProduction SQL server to the Production Server

Linking the pre-production SQL to our Production SQL server provided several advantages. First and foremost it avoided having to perform a database restore into the production server. Second is time was saved by not having to restore the back-up files again.

The exact steps to linking SQL servers is beyond the scope of this post but lots of information can be found online.

6)Run the SQL Update command to restore data from the linked server to the production server

This is a repeat of step 2 but against the production database this time, note the extra node on the paths below, referencing the linked server.

At this point you need to determine if stopping the AOS's is required. Depending on the exact nature of the data being restored the answer may be different. In my situation we choose to the leave the AOS's running.

update T

set T. INVENTDIMID = S .inventdimID

from TCI_AX2012R2_PROD. dbo.BOM as T

join AXSQLUAT. AX2012_RefreshSource.dbo .bom as S
on T. RECID = s .RECID

where T. DATAAREAID = 'TIND'

When doing this make sure that the host server and the linked server are on the same local network, bandwidth and latency will have an affect.


7 & 8) are performed much as 3 & 4 but against the production server.



Using this process I was able to restore data to our production server safely and with no interruption to the end users.

A few thoughts:
  • This process could be improved by restoring the data with an X++ job, this would be a safer method and ensure no update conflicts are caused.
  • The importance of validating invasive processes like this cannot be overstated.