IP Office Technical Tip

Restoring a CCR database to a Different Server PC

The following Technical Tip has been written to assist with the restore of the CCR database to a different instance of SQL Server and resolving orphaned SQL users.

The installation of CCR creates two SQL logons that are used to insert and retrieve call and diagnostic data to and from the database. Information about the mapping of the two CCR SQL Server logins to the CCR database is stored within the newly restored database; it includes the name of the database user and the SID of the corresponding SQL Server logon.

A database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can also happen if the database user is mapped to a SID that is not present in the new SQL Server instance. The most common reason for orphaned SQL users is when databases are moved from one server to another.

A database may be restored, copied or moved in a number of ways using various methods. Please refer to your SQL Server documentation for information on how to execute your preferred method. For the purpose of this document the database will be restored with the method described in order to demonstrate the orphaned user problem. This method will overwrite any existing data with the backup data on the new server.

In order to successfully restore the CCR database the following steps will need to be carried out:

  1. Backup the CCR database that is to be moved. Please refer to the CCR Installation manual for database backup details.

  2. Run the CCR installation on the target server that will be hosting the new CCR database. Install only the database component. Please refer to the CCR Installation manual for installation details.

  3. Once the CCR database installation is complete restore the CCR database. Please refer to the CCR Installation manual for database restore details.

  4. Once the restore is complete a check for orphaned users will need to be carried out. Run the following command in a query window: EXEC sp_change_users_login 'Report' (see fig 4.1)


Figure 4.1

The results show two orphaned users: AvayaSBCCManager and AvayaSBCCUser.

The two orphaned users can be resolved by typing the following commands in a query window:

EXEC sp_change_users_login 'Auto_Fix', 'AvayaSBCCUser' (see fig 4.2)


Figure 4.2

EXEC sp_change_users_login 'Auto_Fix', 'AvayaSBCCManager' (see fig 4.3)


Figure 4.3

The information output in the message pane describes the action taken by SQL to resolve any orphaned users.