Refresh Dev. environment Database.

I recently needed to refresh my development environment with Production data. In the past, I have used pipelines to do that, so if you have an infra team you can ask them to create a pipeline for you ;). Unfortunately, I don't have sufficient permission to create a pipeline so I tried to do it manually.

To begin, you need to export the database backup into the Asset library. Once, you complete it, then you need to download it on the environment on which you want to restore the database backup. Lastly, you need to restore it to the SQL server on the Development environment by using the following command

SqlPackage.exe /a:import /sf:C:\<Backupfile location> /tsn:EnvironmentName /tdn:<NewDatabaseName> /p:CommandTimeout=1200 /ttsc:true

it took some time to restore based on the size of your database. Once it is completed, you can change the name of the database using the following SQL script

USE master

GO

ALTER DATABASE AxDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB

Modify Name = AxDB_Old

GO

ALTER DATABASE AxDB_Old

SET MULTI_USER

GO

This will change the name of your old database to postfix with _OLD. Now execute the same script using new database name, for example

USE master

GO

ALTER DATABASE UAT29102023

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE UAT29102023

Modify Name = AxDB

GO

ALTER DATABASE AxDB

SET MULTI_USER

GO

Open the VS and do the database syn to make sure all your changes which are not move to production environment get syn.