Azure DevOps Copy Sitecore Database

Created: 12 Jun 2020, last update: 8 Jul 2020

Azure DevOps Copy Sitecore Database

A solution to keep the DTAP-street up to date with recent production content is regularly copy the Sitecore databases, and especially the master database. Optionally also copy the web database then you do not have to do a full publish. Optional or do not copy the core database, this contains the Sitecore users if you use the default authentication. so if you copy the core also all Sitecore users are copied including credentials.

So to keep it simple, for example to put production content on development, replace the development master and web database with that from production.

Automate that with Azure DevOps. Sync Sitecore production database with QA/ DEV environment with a button push.

Step1) Copy master database
Azure PowerShell

#copy Sitecore database, for Azure SQL with ElasticPool

$TargetResourceGroup = '$(TargetResourceGroup)'
$SourceResourceGroup = '$(SourceResourceGroup)'
$TargetDbservername = '$(TargetDBServerName)'
$SourceDbservername = '$(SourceDBServerName)'
$ElasticPoolName = '$(TargetDBElasticPoolName)'
$dbname = 'master-db'

Enable-AzureRmAlias
Remove-AzureRmSqlDatabase -ResourceGroupName $TargetResourceGroup -ServerName $TargetDbservername -DatabaseName $dbname -Force -ErrorAction SilentlyContinue
New-AzureRmSqlDatabaseCopy -ResourceGroupName $SourceResourceGroup -ServerName $SourceDbservername -DatabaseName $dbname -CopyResourceGroupName $TargetResourceGroup -CopyServerName $TargetDbservername -CopyDatabaseName $dbname  -ElasticPoolName $ElasticPoolName 

Step2) Set database user
Azure PowerShell

#bind the database sitecoredbuser login to the SQL server sitecoredbuser login
# Install SQL Module
Install-PackageProvider -Name NuGet -Force -Scope CurrentUser

Write-Host "installing modules: start"
Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
Write-Host "installing modules: end"

$params = @{
  'Database' = 'master-db'
  'ServerInstance' =  '$(TargetDBServerName).database.windows.net'
  'Username' = 'sitecoredba'
  'Password' = '$(TargetSitecoreDbaPassword)'
  'OutputSqlErrors' = $true
  'Query' = 'ALTER USER sitecoredbuser WITH Login = sitecoredbuser'
  }

  Invoke-Sqlcmd  @params

Step3) Cleanup Event table
Azure PowerShell

#Cleanup event table because the event queue contains records based on the copy environment
Install-PackageProvider -Name NuGet -Force -Scope CurrentUser

Write-Host "installing modules: start"
Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
Write-Host "installing modules: end"

$params = @{
  'Database' = 'master-db'
  'ServerInstance' =  '$(TargetDBServerName).database.windows.net'
  'Username' = 'sitecoredba'
  'Password' = '$(TargetSitecoreDbaPassword)'
  'OutputSqlErrors' = $true
  'Query' = 'delete from EventQueue'
  }

  Invoke-Sqlcmd  @params

Step4) Cleanup properties table
Azure PowerShell

#Cleanup properties table this table contains records about the Last Published and Last Index Rebuild of each machine.
Install-PackageProvider -Name NuGet -Force -Scope CurrentUser

Write-Host "installing modules: start"
Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
Write-Host "installing modules: end"

$params = @{
  'Database' = 'master-db'
  'ServerInstance' =  '$(TargetDBServerName).database.windows.net'
  'Username' = 'sitecoredba'
  'Password' = '$(TargetSitecoreDbaPassword)'
  'OutputSqlErrors' = $true
  'Query' ="delete From [Properties] where [Key] like 'EQSTamp%'"
  }

  Invoke-Sqlcmd  @params

Step5) Repeat step 1,2,3 and 4 for web database.

Step6) Restart Sitecore to clear Cache

If your on Azure PaaS, App services
task:Azure App Service manage
action: Restart App Services

Aftercare) At least the sitecore_master_index and sitecore_web_index need a rebuild. for SXA also sitecore_sxa_master_index and sitecore_sxa_web_index you can do it manually, or by code see rebuild-search-indexes or for Azure Search just drop the index between Step 5 and 6.

And of course the database need to match with the codebase, remember the database contain also all templates and rendering, so get production data, and do a release including unicorn/TDS sync with your latest code to test it against production data or somethings.

Readmore:

https://hishaamn.wordpress.com/2016/04/20/sitecore-database-migration/

Other solutions: RAZL, Sitecore Sidekick