Manage DQS Databases

  • Article

This section provides information about database management activities that can be performed on the DQS databases such as backup/restore or detach/attach.

In This Topic

  • Backup and Restore the DQS Databases

  • Detach/Attach the DQS Databases

Backup and Restore the DQS Databases

Backup and restore of SQL Server databases are common operations that database administrators perform for preventing loss of data in a case of disaster by recovering data from the backup databases. Data Quality Server is primarily implemented by two SQL Server databases: DQS_MAIN and DQS_PROJECTS. The backup and restore procedures of the Data Quality Services (DQS) databases are similar to any other SQL Server databases.There are three challenges that are associated with backup and restore of the DQS databases:

  • The backup and restore operations of the DQS databases must be synchronized. Otherwise the restored Data Quality Server will not be functional.

  • The two DQS databases, DQS_MAIN and DQS_PROJECTS, contain assemblies and other complex objects, apart from just simple database objects (such as tables and stored procedures).

  • There are some entities outside of the DQS databases that must exist for the DQS databases to be functional as Data Quality Server, specifically the two SQL Server logins (##MS_dqs_db_owner_login## and ##MS_dqs_service_login##), and an initialization stored procedure (DQInitDQS_MAIN) in the master database.

For detailed information about backup and restore in SQL Server, see Back Up and Restore of SQL Server Databases.

Default Autogrowth Size and Recovery Model for the DQS Databases

To prevent DQS databases and transaction logs to grow infinitely and potentially fill the hard disk:

  • The default Autogrowth size of the DQS databases is set to 10%.

  • The default recovery model of the DQS databases is set to Simple. In the Simple recovery model, transactions are minimally logged, and the log truncation happens automatically after the transaction is complete to free up space in the transaction log (.ldf file). For detailed information about the simple recovery model, see Full Database Backups (SQL Server).

Important

  • In the Simple recovery model, when log records remain active for a long time (for example, a long and time-consuming transaction), log truncation can be delayed, and therefore can result in the filling up of transaction log. Also, log truncation does not reduce the size of the physical log file (.ldf file). To reduce the size of a physical log file, you need to shrink the log file. For information about troubleshooting issues around transaction log, see The Transaction Log (SQL Server) or the Microsoft Support article at https://go.microsoft.com/fwlink/?LinkId=237446.

  • You must regularly perform a Full or Differential backup of the DQS databases and back up the transaction log as well to perform point-in-time recovery of data. For more information, see Full Database Backups (SQL Server) and Back Up a Transaction Log (SQL Server).

Detach/Attach the DQS Databases

You can detach the data and transaction log files of the DQS databases, and then reattach the databases to the same or another instance of SQL Server if you want to change the DQS databases to a different instance of SQL Server on the same computer or to move the database.

For detailed information about things to consider before and during detaching and attaching databases in SQL Server, see Database Detach and Attach (SQL Server).

Task Description

Topic

Describes how to back up and restore the DQS databases.

Backing Up and Restoring DQS Databases

Describes how to detach and attach the DQS databases.

Detaching and Attaching DQS Databases

See Also

Concepts

DQS Administration