Recently I’ve had to recover a SCOM environment. This process required me to restore the SQL backups of both the OperationsManager and Data Warehouse databases.

After stopping the SDK service on our RMS, trying to restore the backup from the management studio under Tasks > Restore  > Database I came across a frustrating error “Exclusive access could not be obtained as the database is in use.”

A bit of research led me to an easier way to perform the restore, as well as check what is blocking the exclusive access,

First in SQL management studio select your master database and click new query:

USE MASTER
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-This will make it so only one connection to the database can be made.
-Run the following command to see where any recurring connections to database are coming from.

EXEC SP_WHO2

-Check this list, looking under the DBName column.  If the database is listed, check the ProgramName, and HostName column to see who is attempting to connect.
-If it is not a service, or other application that would automatically reconnect which can be shut down, note the number in the SPID column to kill the connection, and immediately begin the backup.  Replace SPID below with just the number.

KILL SPID
RESTORE DATABASE DATABASENAME FROM DISK = ‘X:\PATHTO\BACKUP.BAK’
GO

-If this completes successfully, we can set the newly restored database back to multi user mode.

ALTER DATABASE DATABASENAME SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

After restoring your OperationsManager database you will also have to re-enable your SQL Broker as it is required in order for your SCOM discoveries to work.

To check if your SQL broker is enabled run the following query, returning a value of ‘0’ means that the Broker is disabled.

SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

To enable the Broker user the following queries:

ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER