Backing Up and Restoring DQS Databases
Applies To: SQL Server 2016
This topic describes how to back up and restore the DQS databases.
You must know or remember the password for the database master key that that you provided during the DQS server installation.
Ensure that there are no running activities or processes in DQS. This can be verified using the Activity Monitoring screen. For detailed information about working in this screen, see Monitor DQS Activities.
Ensure that there are no users logged on the DQS server.
Your Windows user account must be a member of the sysadmin fixed server role in the SQL Server instance to perform the backup and restore operations.
You must have the dqs_administrator role on the DQS_MAIN database to terminate any running activities or stop any running processes in DQS.
Start Microsoft SQL Server Management Studio, and connect to the appropriate SQL Server instance.
In Object Explorer, expand the Databases node.
Back up the DQS_STAGING_DATA database. For step-by-step instructions for backing a SQL Server database, see Create a Full Database Backup (SQL Server).
Back up the DQS_PROJECTS database.
Back up the DQS_MAIN database.
Disconnect from the current instance of SQL Server, and connect to the SQL Server instance where you want to restore these databases.
Restore DQS_MAIN database. For step-by-step instructions to restore a SQL Server database, see Restore a Database Backup Using SSMS.
Restore the DQS_PROJECTS database.
Restore the DQS_STAGING_DATA database.
In Object Explorer, right-click the server, and then click New Query.
In the Query Editor window, copy the following SQL statements, and replace <PASSWORD> with the password that you provided during the DQS installation for the database master key:
USE [DQS_MAIN] GO EXECUTE [internal_core].[RestoreDQDatabases] '<PASSWORD>' GO
Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully.