Whenever you want to restore your Xdb data to another environment there are certain steps to take. I will take you through the steps to accomplish this in this post. The described process is valid for Sitecore 9 and Azure PAAS environments and consists of a total of three tasks:
Restoring or migrating your Xdb data
In the example below we will restore the Xdb data from production to an acceptance environment. This example will also suit your needs when you want to migrate your Xdb data to another solution.
Whenever you want to restore your Xdb data to another environment you will need to restore three databases. Whereas I would suggest to take a look at the post from Jason St-Cyr to get some in depth knowledge on the sharding mechanism Sitecore uses: click here.
Please copy your production shard0db, shard1db and refdatadb to the SQL server that you want to restore your data on and add a suffix to the database names, for example ‘_prdcopy’. You can do this via the Azure Portal, Powershell, CLI and/or SQL Azure Data Studio (or SMSS if you like).
When you have restored the databases please rename the current shard0db, shard1db and refdatadb to make sure you can rollback in case needed.
You can rename the, to be replaced, databases using the following queries:
ALTER DATABASE [yourenvironment-acc-xp1-shard0db] MODIFY NAME = [yourenvironment-acc-xp1-shard0db_backupCurrentDate];
ALTER DATABASE [yourenvironment-acc-xp1-shard1db] MODIFY NAME = [yourenvironment-acc-xp1-shard1db-backupCurrentDate];
ALTER DATABASE [yourenvironment-acc-xp1-refdatadb] MODIFY NAME = [yourenvironment-acc-xp1-refdatadb-backupCurrentDate];
If you happen to have geo-replication activated you might run into the error below:
Msg 40680, Level 16, State 1, Line 1
The operation cannot be performed since the database ‘yourenvironment-acc-xp1-shard0db’ is in a replication relationship.
To avoid this error please stop the replication and retry the rename of your database(s).
Now rename the restored databases to the database names that were used in the environment you are restoring.
ALTER DATABASE [yourenvironment-prd-xp1-shard0db_prdcopy] MODIFY NAME = [yourenvironment-acc-xp1-shard0db];
ALTER DATABASE [yourenvironment-prd-xp1-shard1db_prdcopy] MODIFY NAME = [yourenvironment-acc-xp1-shard1db];
ALTER DATABASE [yourenvironment-prd-xp1-refdatadb_new] MODIFY NAME = [yourenvironment-acc-xp1-refdatadb];
Now that you have restored the databases, please assure you alter the credentials of the corresponding users to the values that are used within this environment.
ALTER USER xcsmmuser WITH PASSWORD = ‘XXX’; // for the shards databases ALTER USER xcrefdatauser WITH PASSWORD = ‘XXX’; // for the reference database
Re-activate the geo-replication, in case you deactivated this before, to assure your data is available in case of disaster recovery.
Now proceed to the next task, rebuild your reporting database. Click here.