Database snapshots

If you need to debug something that will destroy your test data, that is not easy to recreate (i.e. posting a complex journal), then normal approach would be to take DB backup, and restore it after you finished debugging to debug again/test the fix.

Database snapshot is a better option in this case - it is lightning fast (15 seconds to restore 80GB database).

SQL to create snapshot:

CREATE DATABASE AxDB_Snapshot ON
(Name ='AxDB', -- this is the Logical Name value from database Properties / Files / Database files
FileName='J:\Snapshots\AxDB_Snapshot.snp') -- ensure the drive has enough space to store the database
AS SNAPSHOT OF AxDB;

select name, physical_name from AxDB.sys.database_files;

To restore from snapshot:

Microsoft Dynamics 365 Unified Operations: Batch Manageme
Microsoft Dynamics 365 Unified Operations: Data Import Ex
Management Reporter 2012 Process Service
World Wide Web Publishing Service
IIS Express 

USE AxDB

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE master

RESTORE DATABASE AxDB from DATABASE_SNAPSHOT = 'AxDB_Snapshot'

ALTER DATABASE AxDB SET MULTI_USER WITH NO_WAIT

Start d365fo services from powershell: Start-D365Environment

SQL to delete the snapshot:

DROP DATABASE AxDB_Snapshot;

SQL to list database snapshots:

SELECT dbs.name AS logical_name,
    mf.database_id,
    dbs.source_database_id,
    mf.type_desc,
    mf.name AS db_name, 
    mf.physical_name
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
    ON mf.database_id = dbs.database_id
WHERE source_database_id IS NOT NULL

Troubleshooting

Error: Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

- Multiple snapshots of a single database are not supported. Leave only a single snapshot and drop all others.

- You are not specifying correctly the logical name of the snapshot. Run the query to list database snapshots and specify the name correctly.