When you are working on a really hard-to-reproduce issue in D365FO and finally manage to do it, you wish there was an option to save this database state and quickly get back to it. Of course, you can always do a full AxDB database backup and restore it every time after an unsuccessful debug attempt, but it takes minutes to do.

Create snapshot Link to heading

Fortunately, SQL server provides a quicker option - database snapshots. To create one, simply run the following query against AxDB:

CREATE DATABASE AxDB_MAT ON (
    NAME = XXX, 
    FILENAME ='J:\MSSQL_BACKUP\AxDB_MAT.ss')  
AS SNAPSHOT OF AxDB;

AxDB_MAT is the name of the snapshot, feel free to use your own, possibly including a case id. But you need to change XXX to a logical name used in SQL Server for an AxDB database. One of the ways to get is to simply run the above command with XXX and you’ll see the required name in the error message.

The execution should take less than a minute and you should see the newly created snapshot in the Databases -> Database Snapshots folder in Object explorer. You can also create multiple database snapshots - simply run this query again with a different snapshot and filename. One caveat is - SQL Server does not allow you to restore the database from a snapshot if there is more the one of them. So simply remove all except the one you need before the next step.

Restore from snapshot Link to heading

To restore from a snapshot database needs to be temporarily switched to a single-user mode. That is unfortunate because it means you will have to stop IIS and Batch services before the restore and it always takes some time to bring them back up. If you know how to avoid it - please share in the comments below.

Other than that, restoring from a snapshot is also quite easy - simply run the following query against AxDB, replacing AxDB_MAT with the name you used:

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE AxDB FROM 
    DATABASE_SNAPSHOT = 'AxDB_MAT';  
ALTER DATABASE AxDB SET MULTI_USER;

This operation is also way quicker than a full restore and should only take about a minute. After that start IIS and Batch services back up and your reproduction scenario is ready for another debug attempt. Good luck!