DB Restore

 DB Restore:  

SqlPackage.exe /a:import /sf:C:\DBBackup\SEDUAT1backup.bacpac /tsn:localhost /tdn:AxDBNew2 /ttsc:true /p:CommandTimeout=1200 

ALTER DATABASE [YourOldDatabaseName] MODIFY NAME = [YourNewDatabaseName];

1. LCS-->Asset library-->Database backup files-->download required bacpac file. 

2.Sql server-->take old DB back up. 

3. Download SqlPackage.exe if not available in system. and extract the package. 

4.Run the below sql cmd: 

Cd Users: 

Note : There must be no spl characters in the name of bacpac file. If any spl char is there in the name like - _ \ cmd won't run. and we'll get below error : 

Operation Import requires that the target be defined. 

Ex:  Name of the bacpac file should not be like this Cumulus-UATbackup 

         Name of the bacpac file should be like this CumulusUATbackup 

C:\Users\Admin3e268b225d\Downloads\Sqlpackage\Sqlpackage>SqlPackage.exe/a:import /sf:K:\BackupDB\CumulusUATbackup.bacpac/tsn:localhost  /tdn:AXDB_Prod /p:CommandTimeout=1200 

=>C:\Users\Admin3e268b225d\Downloads\Sqlpackage\Sqlpackage ---> The path where downloaded sql package has available. 

=>sf:K:\BackupDB ---> Path of bacpac file. 

=> CumulusUATbackup.bacpac ---> bacpac file name. 

=>tdn:AXDB_Prod ---> New db name. 

5. After successful file import, Update the db using below logic :  

* CREATE USER axdeployuser FROM LOGIN axdeployuser 

EXEC sp_addrolemember 'db_owner', 'axdeployuser' 


CREATE USER axdbadmin FROM LOGIN axdbadmin 

EXEC sp_addrolemember 'db_owner', 'axdbadmin' 


CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser 

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser' 

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser' 

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
  
CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser 
  
CREATE USER axdeployextuser FROM LOGIN axdeployextuser 

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE] 

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE' 

UPDATE T1 

SET T1.storageproviderid = 0 

    , T1.accessinformation = '' 

    , T1.modifiedby = 'Admin' 

    , T1.modifieddatetime = getdate() 

FROM docuvalue T1 

WHERE T1.storageproviderid = 1 --Azure storage 

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking 

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2 

GO 

-- Begin Refresh Retail FullText Catalogs 

DECLARE @RFTXNAME NVARCHAR(MAX); 

DECLARE @RFTXSQL NVARCHAR(MAX); 

DECLARE retail_ftx CURSOR FOR 

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES 

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG'); 

OPEN retail_ftx; 

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;   

BEGIN TRY 

    WHILE @@FETCH_STATUS = 0  

    BEGIN  

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME; 

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate'; 

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION'; 

        EXEC SP_EXECUTESQL @RFTXSQL; 

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME; 

    END 

END TRY 

BEGIN CATCH 

    PRINT error_message() 

END CATCH  

CLOSE retail_ftx;  

DEALLOCATE retail_ftx;  

-- End Refresh Retail FullText Catalogs 

--Begin create retail channel database record-- 

declare @ExpectedDatabaseName nvarchar(64) = 'Default'; 

declare @DefaultDataGroupRecId BIGINT; 

declare @ExpectedDatabaseRecId BIGINT;  

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName) 

BEGIN  

select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default';  

insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE) 

values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0);  

select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName;  

insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE) 

select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT 

inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID 

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0 

END;  

--End create retail channel database record * 

6.  turn on change tracking using below logic :  

ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON); 

7.  Start to use the new database  

To switch environments and use the new database, first stop the following services: 

World Wide Web Publishing Service 

Microsoft Dynamics 365 Unified Operations: Batch Management Service 

Management Reporter 2012 Process Service 

After these services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.  

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services. 

DB restore from PROD to UAT/Sandbox and UAT/Sandbox to Dev box | Chat | Microsoft Teams