How To Refresh DQS (Data Quality Services) Databases in SQL Server : Demo

Hi Guys

I am new to DQS and have started with lot of issues. In My comp we get a job to refresh all 3 DQS databases from Production to test machine.

Sounds very easy but not really. As DQS is dependent extensively on Assembly's and & dotnet frame work along with CLR there is a possibility you might face lot of issues.

So the best way is to follow below steps:

1. Take backup of prod databases and copy on local server
2. Take backup of existing non-prod DQS databases and keep it safe
3. Take user permission script in non prod environment, to apply after restore.
4. Restore all 3 databases in non-prod from prod backups
5. Apply all backed up permission taken earlier.
6. Make sure to fix all orphan users , Specially used for DQS
7. Make sure your all 3 DQS databases have owner and it should not be sa, Use some windows account
8. Set all 3 DQS databases to trustworthy using below SQL

ALTER DATABASE DQS_Main SET TRUSTWORTHY ON
ALTER DATABASE DQS_STAGING SET TRUSTWORTHY ON
ALTER DATABASE DQS_STAGING_DATA SET TRUSTWORTHY ON

8. Go to command prompt and upgrade all  dll using below command
Locate the DQSInstaller.exe file. If you installed the default instance of SQL Server, the DQSInstaller.exe file will be available at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn

"dqsinstaller.exe -upgradedlls"

9. Once All dll upgrade , upgrade master key of prod environment on non prod environment 

using below sql code. In case it failed (I will update how to register Assemblies manually)

USE [DQS_MAIN]
GO
EXECUTE [internal_core].[RestoreDQDatabases] ''
GO

10. If your master key is wrong then there are couple of ways to fix that but

 of-course not recommended by Microsoft. 

I will add in my next blog post (how to by pass master key in DQS)

And your Non prod server is ready to be used