AXDB Database Take and restore SQLSnapshot in D365 Finance and Operations

Hi DaxPeople,

Welcome back to the blog. If you need to take SQL Snapshot of Database and restore it to the previous snapshot then you came to the right place.

It is always recommended to take SQL snapshot before performing complex queries which in-turn updates multiple tables in database. Once the testing is done, you can always restore the database with earlier snapshot.

Take SQL Database Snapshot in Microsoft Dynamics 365 F&O:

Pre-requisites to be completed before taking the database snapshot in SQL.

Step 1 - Close the Visual Studio Integrated Development Environment (if opened)

Step 2 - Stop the below mentioned services in services.msc window

• World wide web publishing services
• Management reporter services
• Microsoft Dynamics 365 Unified operations : Batch Management services
• Microsoft Dynamics 365 Unified operations: Data import export Management services.

Step 3 - Open Internet Information services Manager (IIS)  and stop the application.

Step 4 - Ask other users to disconnect the VM .

Take AXDb snapshot : 

You can execute below SQL query in Master DB , which will create Snapshot of existing version of database.

USE [master]; 

CREATE DATABASE  AXDb14042020_Demo ON  

( NAME = AXDb_latestPC, 

FILENAME = 'G:\MSSQL_DATA\AXDb14042020_Demo.ss' )  

AS SNAPSHOT OF AXDb ;  

GO 

Understanding the terms and keywords used in Query :

-- Master : is the Db on which below query to be executed

-- AXDb14042020_Demo : is the Snapshot name

-- AXDb_latestPC : is the logical name of the Database. It can be found under AXDb > Properties > Files > Logical name.

--'G:\MSSQL_DATA\AXDb14042020_Demo.ss'  : is the snapshot filepath where it gets stored.

-- AXDb : is the source database name.


After you execute the script, the database snapshot gets added to the SQL under Database Snapshots (refresh the node if you can't find it).

Taking the database version of snapshot is successful.


Restore snapshot to SQL Database in Microsoft Dynamics 365 F&O:

For restoring the snapshot also we need to follow the same pre-requisites mentioned above.

Once pre-requisites are completed, execute below query which will restore the snapshot.

USE [master];
GO
RESTORE DATABASE AXDb FROM DATABASE_SNAPSHOT ='AXDb14042020_Demo'


Understanding the terms and keywords used in Query :

-- AXDb : is the source database name to which Snapshot to be restored.

-- AXDb14042020_Demo : is the Snapshot name, which will be restored to source database.

Most of the time, users will face the issue while DB restore.

Database state cannot be changed while other users are using the database "XX".

To resolve this issue, execute below script which will remove active users from environment and then re-execute the restore snapshot script.

USE [master];

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('AXDb')

EXEC(@kill);

Understanding the terms and keywords used in Query :

-- AXDb : is the source database name from which users sessions will be closed.


Also, sometimes taking Database offline and bring online will solve the multiple users issue.


Happy Daxing ..!!