How to Back Up and Restore Service Reporting Databases

 

Updated: May 13, 2016

Applies To: System Center 2012 R2 Service Reporting

The databases in a System Center 2012 R2 Service Reporting environment are as follows:

  • UsageDatawarehouseDB

  • UsageETLRepositoryDB

  • UsageStagingDB

  • UsageAnalysisDB

If a computer that hosts a database fails, all you need for recovery is the ability to restore the database to a computer that has the same name as the original computer. You should base your disaster recovery strategy for the Service Reporting databases on procedures for general Microsoft SQL Server disaster recovery. For more information, see Back Up and Restore of SQL Server Databases.

When you back up a database, security information such as SQL Server users and roles are backed up with the database. If user’s domain credentials are used by SQL Server users and roles and they differ on the computer that you restore to, you must manually update the domain credentials by using SQL Server Management Studio for the database that is restored.

You can restore Service Reporting databases as an optional step when you install Service Reporting, or you can restore databases by overwriting existing databases. After you have restored databases and updated SQL Server users and roles (if necessary), you can register Service Reporting data source systems, including System Center 2012 R2 Operations Manager and Windows Azure Pack for Windows Server, to reestablish connections to the data source systems.

In the event of Windows Azure Pack for Windows Server Usage database restoration, you must ensure that Service Reporting usage data is synchronized with Windows Azure Pack. To ensure that data is synchronized between the two systems, ensure that you perform the “To synchronize restored Windows Azure Pack usage data with Service Reporting” procedure below.

In the event that you need to recreate the UsageAnalysisDB, you can rerun the PostDeploymentConfig.ps1 Windows PowerShell® script using the procedure below.

To back up a Service Reporting database

  1. Start SQL Server Management Studio, select the database to back up, right-click the database, point to Tasks, and then click Back Up.

  2. In the Back Up Database dialog box, choose your database backup options, and then click OK to back up the database.

  3. When the backup is complete, click OK to close the Back Up Database dialog box.

To restore a Service Reporting database

  • If you are restoring a database as part of a new Service Reporting installation, perform the steps for installing Service Reporting at How to Install Service Reporting. Backed-up databases are found as part of Setup.

  • If you are restoring a database and overwriting an existing database, perform the following steps:

    1. Start SQL Server Management Studio, select the database to restore, right-click the database, point to Tasks, point to Restore, and then click Database.

    2. In the Restore Database dialog box, choose your database restoration options, and then click OK to restore the database.

    3. When the restoration is complete, click OK to close the Restore Database dialog box.

To register Service Reporting with data source systems

To recreate the UsageAnalysisDB

  1. On the server where you want to restore the database, start Windows PowerShell as an administrator.

  2. Browse to the InstallationDrive:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance folder. For example, type cd “c:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance” and then press Enter.

  3. Type .\PostDeploymentConfig.ps1 –User  UserName  -PasswordPassword and replace the UserName and Password values with the credentials that are used to connect to Windows Azure Pack. Then, press Enter.

    If the script is successful, it displays progress for Windows Azure Pack and Operations Manager registration, along with information about the data warehouse module. Exit Windows PowerShell.

To synchronize restored Windows Azure Pack usage data with Service Reporting

  1. Stop the Windows Azure Pack Usage and UsageCollector services to prevent any new usage events from being added to the system during the failure recovery.

  2. Restore the Windows Azure Pack data from a saved backup.

  3. Make note of the last usage event ID by running the following SQL query against the Windows Azure Pack Usage database.

    SELECT MAX([RecordId])  
    FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]  
    
  4. Synchronize the recorded usage event ID by running the following SQL query against the Service Reporting UsageETLRepositoryDB database.

    DECLARE @LastWAPEventId INT = <EventId>  
    DECLARE @CurrEventId INT  
    SELECT  @CurrEventId = InputString  
    FROM    [UsageETLRepositoryDB].[dbo].[ProcessModuleInput]  
    WHERE   InputName = '@StartEventId'  
    
    IF @CurrEventId > @LastWAPEventId  
    BEGIN  
        UPDATE  [UsageETLRepositoryDB].[dbo].[ProcessModuleInput]  
        SET     InputString = @LastWAPEventId  
        WHERE   InputName = '@StartEventId'  
    END  
    

    Replace <EventId> in the above query with the value that you made note of from step 3.

  5. Start the Windows Azure Pack Usage and UsageCollector services.

See Also

Service Reporting in System Center 2012 R2
Operating Service Reporting