Backing Up Service Manager Databases

Updated: May 13, 2016

Applies To: System Center 2012 SP1 - Service Manager, System Center 2012 R2 Service Manager, System Center 2012 - Service Manager

There are up to eight databases in a System Center 2012 – Service Manager environment:

• ServiceManager

• DWDataMart

• DWRepository

• DWStagingAndConfig

• ReportServer

• Analyst

• OMDWDataMart

• CMDWDataMart

The first four databases in this list need to connect and exchange data with the Service Manager and data warehouse management servers. Data is encrypted during these exchanges. On the management servers, the encryption keys are backed up and restored as necessary, as explained in this guide. For more information about the encryption keys on the management servers, see Backing Up Service Manager Management Servers. For the servers that host databases, the encryption keys are stored in the databases themselves.

If a computer that hosts a database fails, all you need for recovery is the ability to restore the databases, which include the encryption keys, to a computer with the same name as the original computer. Your disaster recovery strategy for the Service Manager databases should be based on procedures for general SQL Server 2008 disaster recovery. For more information, see SQL Server 2008 R2 Books Online: Planning for Disaster Recovery.

As part of your disaster recovery preparation, you run a script to capture the Security log to preserve user role information for each database. After you deploy Service Manager and, if necessary, run the Data Warehouse Registration Wizard, you use the SQL Server Script Wizard to create a script that captures SQL Server logon permissions and object-level permissions. Then, if you need to restore a new server for the Service Manager databases, you can use this script to recreate the necessary logon permissions and object-level permissions. The wizard that is used to generate scripts in SQL Server 2008 differs from the wizard in SQL Server 2008 R2. Instructions for both the SQL Server 2008 wizard and the SQL Server 2008 R2 wizard are presented in this guide.

During installation of the Service Manager database, Service Manager Setup enables common language runtime (CLR) on the computer that is running SQL Server. If you restore a Service Manager database to another computer running SQL Server, you must enable CLR manually. For more information, see Enabling CLR Integration.

How to Start the SQL Server 2008 Script Wizard
Describes how to generate a script to capture SQL Server 2008 logon permissions and object-level permissions.

How to Start the SQL Server 2008 R2 Script Wizard
Describes how to generate a script to capture SQL Server 2008 R2 logon permissions and object-level permissions.