Programmatically find MS Shipped Databases


Hello,

Have you ever needed to programmatically find what databases in you server are part of Microsoft products? Maybe to use that information in your own piece of code?

In my case, I needed that information to exclude Sharepoint and Biztalk databases from the AdaptiveIndexDefrag cycles that are part of the maintenance operations we deploy in customers (implemented by the related  job, not the procedure itself), and some other internal uses.

So with the help of several engineers around the globe, I have gathered a matrix of products and their databases. Because some products do not enforce a fixed database name, we also needed some notable schema objects that would allow us to identify those databases.

Here’s the result of that shared effort:

Product

Database Name(s)

Notable schema objects

References

ASP.NET Session State

ASPState

ASPStateTempSessions;
ASPStateTempApplications

http://support.microsoft.com/kb/317604

http://msdn.microsoft.com/en-us/library/hh948178.aspx

BizTalk 2004

BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain
BizTalkAnalysisDb
BizTalkHwsDb
BizTalkEDIDb
BAMPrimaryImportsuccessfully
TPM

BizTalkDBVersion

http://msdn.microsoft.com/en-us/library/ee251618(v=bts.10).aspx

BizTalk 2004 / 2006R2 / 2009 / 2010

SSODB

SSOX_<variable>

 

Biztalk 2006R2

BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain
BizTalkAnalysisDb
BizTalkHwsDb
BizTalkEDIDb
BAMPrimaryImport
TPM

BizTalkDBVersion

http://msdn.microsoft.com/en-us/library/aa578342(v=bts.20).aspx

BizTalk 2009 / 2010 / 2013

BizTalkMgmtDb
BizTalkDTADb
BizTalkMsgBoxDb
BizTalkRuleEngineDb
BAMPrimaryImport
BAMStarSchema
BAMArchive
BAMAnalysis
BAMAlertsApplication
BAMAlertsNSMain

BizTalkDBVersion

http://blogs.msdn.com/b/skesiraju/archive/2009/02/03/indentify-biztalk-version-and-edition.aspx

http://msdn.microsoft.com/en-us/library/aa578342.aspx

http://msdn.microsoft.com/en-us/library/aa578342(v=bts.10).aspx

http://msdn.microsoft.com/en-us/library/aa578342(v=bts.80).aspx

Dynamics AX 2009, Dynamics AX 4.0

No specific name as it varies based on customer preference.

LedgerTrans;
InventTrans;
InventSum

http://msdn.microsoft.com/en-us/library/aa624918(v=ax.50).aspx

Dynamics CRM 2011

MSCRM_Config

Organization;
OrganizationProperties;
Notification

http://msdn.microsoft.com/en-us/library/cc308176.aspx

Dynamics CRM 4.0 / 2011

<variable>_MSCRM

ContactBase;
ContactExtensionBase;
AccountBase;
AccountExtensionBase;

http://msdn.microsoft.com/en-us/library/cc308176.aspx

http://technet.microsoft.com/en-us/library/dd979327.aspx

Dynamics GP 7.5 / 8 / 9 / 10

No specific name as it varies based on customer preference.

DU000020;
PDK_SQL_Options;
PDK_Version;
SY_SQL_Options;
GPS_SQL_Error_Codes;

http://support.microsoft.com/kb/850662

http://www.gptablereference.com/

Dynamics NAV 5.0 / 2009

No specific name as it varies based on customer preference. "Demo Database NAV (6-0)" - default logical file name

User Personalization;
<identifier>$G_L Entry;
<identifier>$Item Ledger Entry;
<identifier>$Bank Account Ledger Entry

http://support.microsoft.com/kb/980653

http://www.microsoft.com/en-us/download/details.aspx?id=24432

FAST Search Server 2010 for SharePoint

FASTSearchAdminDatabase

 

http://technet.microsoft.com/library/cc678868(office.14).aspx

Forefront Endpoint Protection 2010

FEPDW
FEPDB_<variable>

 

http://technet.microsoft.com/en-us/library/gg710933.aspx

Forefront Identity Management 2010

FIMService

 

http://technet.microsoft.com/en-us/library/hh322891(v=ws.10)

Lync 2010

xds
rtc
rtcdyn
rtcab
rtcab1
cpsdyn
rgsdyn
rgsconfig
lis
LcsLog
LcsCDR
QoEMetrics

No specific name for Group Chat database as it varies based on customer preference

FocusJoinsAndLeaves;
MediationServers;
ConferenceSessionDetails;
AppliedBandwidthSource;
PayloadDescription;
PrincipalMemberDifference;
PrincipalAffiliations

http://technet.microsoft.com/en-us/library/gg398370(v=ocs.14).aspx

Lync 2013

xds
rtc
rtcshared
rtcxds
rtcdyn
rtcab
rtcab1
cpsdyn
rgsdyn
rgsconfig
lis
lyss
LcsLog
LcsCDR
QoEMetrics

No specific name for Persistent Chat database as it varies based on customer preference

FocusJoinsAndLeaves;
MediationServers;
ConferenceSessionDetails;
AppliedBandwidthSource;
PayloadDescription;
tblPrincipalMemberDifference;
tblPrincipalAffiliations

http://technet.microsoft.com/en-us/library/gg398370.aspx

MediaRoom

BranchDB
vodBackend
ActivityLog
ServiceGroupDB
EventLog
ClientTraceLog
ListingsSettings
tServerController

SQLJobDefinition;
JobStepDefinition;
JobScheduleDefinition

 

MediaRoom

BranchDB
ActivityLog
ServiceGroupDB
ClientTraceLog

MasterInfo

 

MediaRoom

BranchDB
ServiceGroupDB

VodKeyRing

 

PerformancePoint Services

PerformancePoint Service Application_<variable>
PerformancePoint Service_<variable>

 

http://technet.microsoft.com/library/cc678868(office.14).aspx

PowerPivot

DefaultPowerPivotServiceApplicationDB_<variable>

 

http://technet.microsoft.com/library/cc678868(office.14).aspx

http://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2

Project Server 2010

ProjectServer_Draft
ProjectServer_Published
ProjectServer_Archive_<variable>
ProjectServer_Reporting

 

http://technet.microsoft.com/library/cc678868(office.14).aspx

Project Server 2013

ProjectWebApp (Default - can be changed)

 

http://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2

SC Orchestrator 2012

Orchestrator

MaintenanceTasks;
TASK_RUNPROGRAM

 

SCCM 2007

SMS_<variable>

Agents;
DistributionPoints;
DistributionPointGroup;
SysResList

http://www.microsoft.com/en-us/download/details.aspx?id=22052

SCCM 2012

CM_<variable>

Agents;
DistributionPoints;
DistributionPointGroup;
SysResList

http://msdn.microsoft.com/en-us/library/hh948178.aspx

http://connect.microsoft.com/SQLServer/feedback/details/783327/trace-flag-8295

SCCM 2012

SUSDB

   

SCOM 2007R2 / 2012 / 2012R2

OperationsManager
OperationsManagerDW
OperationsManagerAC

ManagedEntity;
MaintenanceMode

http://technet.microsoft.com/en-us/library/gg508713.aspx

SCSM 2010

ServiceManager
DWRepositoryDWStagingAndConfig
DWRepository
DWDataMart

 

http://technet.microsoft.com/en-us/library/ff461229.aspx

SCSM 2012

Service Manager
DWStagingAndConfig
DWRepository
DWDataMart
DWASDataBase
OMDWDataMart
CMDWDataMart

 

http://technet.microsoft.com/en-us/library/hh519598.aspx

Sharepoint 2007

WSS_Search
WSS_Search_Config
SharedServices_DB
SharedServicesN_DB
SharedServices_Search_DB
SharedServicesN_Search_DB
WSS_Content
SharePoint_Admin_Content_<variable>

Versions

http://technet.microsoft.com/en-US/library/cc678868(v=office.12).aspx

Sharepoint 2007

SSO

 

http://technet.microsoft.com/en-US/library/cc678868(v=office.12).aspx

Sharepoint 2010

SharePoint_Config
SharePoint_Admin_Content
WSS_Content
WSS_UsageApplication
Bdc_Service_DB_<variable>
Application_Registry_server_DB_<variable>
SubscriptionSettings_<variable>
Secure_Store_Service_DB_<variable>
StateService
WebAnalyticsServiceApplication_StagingDB_<variable>
WebAnalyticsServiceApplication_ReportingDB_<variable>
Search_Service_Application_DB_<variable>
Search_Service_Application_CrawlStoreDB_<variable>
Search_Service_Application_PropertyStoreDB_<variable>
User Profile Service Application_ProfileDB_<variable>
User Profile Service Application_SyncDB_<variable>
User Profile Service Application_SocialDB_<variable>
Managed Metadata Service_<variable>
WordAutomationServices_<variable>

Versions

http://technet.microsoft.com/library/cc678868(office.14).aspx

SharePoint 2013

SharePoint_Config
SharePoint_Admin_Content
WSS_Content
AppManagement
Bdc_Service_DB_<variable>
Search_Service_Application_Db_<variable>
Search_Service_Application_AnalyticsReportingStoreDB_<variable>
Search_Service_Application_CrawlStoreDB_<variable>
Search_Service_Application_LinkStoreDB_<variable>
Secure_Store_Service_DB_<variable>
SharePoint_Logging
SettingsServiceDB
User Profile Service Application_ProfileDB_<variable>
User Profile Service Application_SyncDB_<variable>
User Profile Service Application_SocialDB_<variable>
WordAutomationServices_<variable>
Managed Metadata Service Application_Metadata_<variable>
SharePoint Translation Services_<variable>
SessionStateService_<variable>

Versions

http://technet.microsoft.com/en-us/library/cc678868.aspx

SQL 2012 Semantic Search

Semanticsdb

 

http://www.microsoft.com/en-us/download/details.aspx?id=29069

SSRS

ReportServer

ChunkData;
ExecutionLogStorage;
Subscriptions;
SegmentedChunk

http://technet.microsoft.com/en-us/library/ms159093.aspx

SSRS

ReportServerTempDB

ChunkData;
ExecutionCache;
SegmentedChunk

http://technet.microsoft.com/en-us/library/ms159093.aspx

SSRS (Sharepoint Integrated Mode)

ReportingService_<variable>
ReportingService_<variable>_Alerting

RSDB

ChunkData;
ExecutionLogStorage;
Subscriptions;
SegmentedChunk

http://technet.microsoft.com/en-us/library/ms157285.aspx

http://technet.microsoft.com/library/cc678868(office.14).aspx#Section7

http://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4

SSRS (Sharepoint Integrated Mode)

ReportingService_<variable>TempDB

RSTempDB

ChunkData;
ExecutionCache;
SegmentedChunk

http://technet.microsoft.com/en-us/library/ms157285.aspx

http://technet.microsoft.com/library/cc678868(office.14).aspx#Section7

http://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4

TFS2010 / TFS 2012

Tfs_Configuration

tbl_AccessMapping;
tbl_CatalogResource;
tbl_NotificationQueue

http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB

TFS2010 / TFS 2012

Tfs_DefaultCollection
Tfs_<variable>

FenceConfiguration;
FencedLabSystemConfiguration;
WorkItemsAre;
WorkItemsWere

http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB

TFS2010 / TFS 2012

Tfs_Warehouse

FactWorkItemLinkHistory;
DimToolArtifactDisplayUrl;
FactWorkItemHistory

http://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB


Here’s a snippet using this information. First list all DBs that are already identifiable by name as not being a MS Shipped DB:

IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
DROP TABLE #tmpdbs
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit)

INSERT INTO #tmpdbs ([dbid], [dbname], isdone)
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND [state] = 0 AND database_id > 4 AND is_distributor = 0
-- Ignore MS shipped databases
AND [name] NOT IN (
    --Semantic Search
    'Semanticsdb',
    --SCSM 2012
    'Service Manager','DWStagingAndConfig','DWRepository','DWDataMart','DWASDataBase','OMDWDataMart','CMDWDataMart',
    --SCSM 2010
    'ServiceManager',
    --Biztalk 2009 / 2010 / 2013
    'SSODB','BAMAnalysis','BAMArchive','BAMAlertsApplication','BAMAlertsNSMain','BAMPrimaryImport','BAMStarSchema','BizTalkMgmtDb','BizTalkMsgBoxDb','BizTalkDTADb','BizTalkRuleEngineDb',
    --Biztalk 2006R2
    'BAMPrimaryImport','BizTalkEDIDb','BizTalkHwsDb','TPM','BizTalkAnalysisDb',
    --Biztalk 2004
    'BAMPrimaryImportsuccessfully',
    --ASP .NET
    'ASPState',
    --Dynamics CRM 2011
    'MSCRM_Config',
    --Forefront Endpoint Protection 2010
    'FEPDW',
    --Lync 2010
    'xds','rtc','rtcdyn','rtcab','rtcab1','cpsdyn','rgsdyn','rgsconfig','lis','LcsLog','LcsCDR','QoEMetrics',
    --Lync 2013
    'lyss','rtcxds',
    --MediaRoom
    'BranchDB','vodBackend','ActivityLog','ServiceGroupDB','EventLog','ClientTraceLog','ListingsSettings','tServerController',
    --SCOM 2007R2 / 2012 / 2012R2
    'OperationsManager','OperationsManagerDW','OperationsManagerAC'
    --SC Orchestrator 2012
    'Orchestrator',
    --Sharepoint 2007
    'SSO','WSS_Search','WSS_Search_Config','SharedServices_DB','SharedServices_Search_DB','WSS_Content',
    --SCCM 2007 / 2012
    'SUSDB',
    --Project Server
    'ProjectWebApp',
    --SSRS
    'ReportServer','ReportServerTempDB',
    --SSRS (Sharepoint Integrated Mode)
    'RSDB','RSTempDB',
    --TFS 2010 / 2012
    'Tfs_Configuration','Tfs_DefaultCollection','Tfs_Warehouse'
    )
    --Dynamics CRM 2011
    AND [name] NOT LIKE '%_MSCRM'
    --Forefront Endpoint Protection 2010
    AND [name] NOT LIKE 'FEPDB_%'
    --SCCM 2007
    AND [name] NOT LIKE 'SMS_%'
    --SCCM 2012
    AND [name] NOT LIKE 'CM_%'
    --SharePoint Server 2007
    AND [name] NOT LIKE 'WSS_Search%'
    AND [name] NOT LIKE 'SharedServices_DB%'
    AND [name] NOT LIKE 'SharedServices_Search_DB%'
    AND [name] NOT LIKE 'SharedServices__DB%'
    AND [name] NOT LIKE 'SharedServices__Search_DB%'
    AND [name] NOT LIKE 'SharedServicesContent%'
    --SharePoint 2010
    AND [name] NOT LIKE 'Secure_Store_Service_DB_%' 
    AND [name] NOT LIKE 'StateService%'
    AND [name] NOT LIKE 'WebAnalyticsServiceApplication_StagingDB_%'
    AND [name] NOT LIKE 'WebAnalyticsServiceApplication_ReportingDB_%'
    AND [name] NOT LIKE 'Search_Service_Application_DB_%'
    AND [name] NOT LIKE 'Search_Service_Application_CrawlStoreDB_%'
    AND [name] NOT LIKE 'Search_Service_Application_PropertyStoreDB_%'
    AND [name] NOT LIKE 'User Profile Service Application_ProfileDB_%'
    AND [name] NOT LIKE 'User Profile Service Application_SyncDB_%'
    AND [name] NOT LIKE 'User Profile Service Application_SocialDB_%'
    AND [name] NOT LIKE 'Managed Metadata Service_%'
    AND [name] NOT LIKE 'WordAutomationServices_%'
    --SharePoint 2013
    AND [name] NOT LIKE 'SharePoint_Admin_Content%'
    AND [name] NOT LIKE 'AppManagement%'
    AND [name] NOT LIKE 'Search_Service_Application_AnalyticsReportingStoreDB_%'
    AND [name] NOT LIKE 'Search_Service_Application_LinkStoreDB_%'
    AND [name] NOT LIKE 'Secure_Store_Service_DB_%'
    AND [name] NOT LIKE 'SharePoint_Logging_%'
    AND [name] NOT LIKE 'SettingsServiceDB%'
    AND [name] NOT LIKE 'SharePoint_Logging_%'
    AND [name] NOT LIKE 'Managed Metadata Service Application_Metadata_%'
    AND [name] NOT LIKE 'SharePoint Translation Services_%'
    AND [name] NOT LIKE 'SessionStateService%'
    --PerformancePoint Services
    AND [name] NOT LIKE 'PerformancePoint Service_%'
    --SharePoint Foundation 2010
    AND [name] NOT LIKE 'SharePoint_Config%' 
    AND [name] NOT LIKE 'SharePoint_AdminContent%'
    AND [name] NOT LIKE 'WSS_Content%'
    AND [name] NOT LIKE 'WSS_UsageApplication%'
    AND [name] NOT LIKE 'Bdc_Service_DB_%'
    AND [name] NOT LIKE 'Application_Registry_server_DB_%'
    AND [name] NOT LIKE 'SubscriptionSettings_%'
    AND [name] NOT LIKE 'SharePoint_AdminContent%'
    --SSRS SP Integrated Mode
    AND [name] NOT LIKE 'ReportingService_%'
    --Project Server 2010
    AND [name] NOT LIKE 'ProjectServer_Draft%'
    AND [name] NOT LIKE 'ProjectServer_Published%'
    AND [name] NOT LIKE 'ProjectServer_Archive_%'
    AND [name] NOT LIKE 'ProjectServer_Reporting%'
    --FAST Search Server for SharePoint 2010
    AND [name] NOT LIKE 'FASTSearchAdminDatabase%'
    --PowerPivot for SharePoint
    AND [name] NOT LIKE 'DefaultPowerPivotServiceApplicationDB%'
    --Project Server
    AND [name] NOT LIKE 'ProjectWebApp%'
    --PerformancePoint Services
    AND [name] NOT LIKE 'PerformancePoint Service_%'
    --TFS 2010 / 2012
    AND [name] NOT LIKE 'Tfs_%';

And then look for notable tables in remaining DBs:

IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblMSDBs'))
DROP TABLE #tblMSDBs;
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblMSDBs'))
CREATE TABLE #tblMSDBs ([dbid] int, [dbname] sysname);

DECLARE @dbname sysname, @dbid int, @sqlcmd NVARCHAR(2000)

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0
    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
IF EXISTS (SELECT name AS [tab_name]
FROM sys.all_objects (NOLOCK) WHERE name IN ('
'ASPStateTempSessions'',''ASPStateTempApplications'',''BizTalkDBVersion'',''LedgerTrans'', ''InventTrans'',''InventSum'',''Organization'',''OrganizationProperties'',''Notification'',''ContactBase'',''ContactExtensionBase'',''AccountBase'',
'
'AccountExtensionBase'',''SQLJobDefinition'',''JobStepDefinition'',''JobScheduleDefinition'',''MasterInfo'',''VodKeyRing'',''ManagedEntity'',
'
'MaintenanceMode'',''MaintenanceTasks'',''TASK_RUNPROGRAM'',''Agents'',''DistributionPoints'',''DistributionPointGroup'',''SysResList'',
'
'Versions'',''ChunkData'',''ExecutionLogStorage'',''Subscriptions'',''SegmentedChunk'',''tbl_AccessMapping'',''tbl_CatalogResource'',
'
'tbl_NotificationQueue'',''FenceConfiguration'',''FencedLabSystemConfiguration'',''WorkItemsAre'',''WorkItemsWere'',''FactWorkItemLinkHistory'',
'
'DimToolArtifactDisplayUrl'',''FactWorkItemHistory'',''User Personalization'',''DU000020'',''PDK_SQL_Options'',''PDK_Version'',''SY_SQL_Options'',
'
'GPS_SQL_Error_Codes'',''FocusJoinsAndLeaves'',''MediationServers'',''ConferenceSessionDetails'',''AppliedBandwidthSource'',''PayloadDescription'',
'
'PrincipalMemberDifference'',''PrincipalAffiliations'',''tblPrincipalMemberDifference'',''tblPrincipalAffiliations'')
OR name LIKE '
'SSOX_%'' OR name LIKE ''%$G_L Entry'' OR name LIKE ''%$%Ledger Entry'')
SELECT '
'' + CONVERT(VARCHAR(10), @dbid) + ''' AS [DBID], ''' + @dbname + ''' AS [DBName]' INSERT INTO #tblMSDBs EXECUTE sp_executesql @sqlcmd UPDATE #tmpdbs SET isdone = 1 WHERE dbid = @dbid END; DELETE #tmpdbs FROM #tmpdbs t INNER JOIN #tblMSDBs tms ON t.[dbid] = tms.[dbid];

Finally, list all DBs in the system that were not identified as MS Shipped DBs:

SELECT dbid,dbname FROM #tmpdbs;

Many thanks to fellow engineers that helped gather much of the raw information behind this: Alex Meyer, Andres Naranjo, Arvind Shyamsundar, Carson Yeung, Chin Seng Goh, Gonçalo Antunes, João Oliveira, João Loureiro, Manuel Semblano, Marco Cibis, Rui Costa, Rui Veloso, Shaun Beane and Todd Haynie.

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

Comments (3)

  1. Aaron Morelli says:

    Wow, very helpful, thank you! As someone who's had to write maintenance procs to handle BizTalk DBs in special ways, I appreciate the nature of the problem. Your hard work is appreciated. 🙂

  2. robert.watkins@fisglobal.com says:

    Most useful.  Thanks.

    N.B. however that it's a table.  Tables contain heterogeneous elements, matrices contain homogeneous elements.

  3. Pedro Lopes says:

    Robert, the term matrix can mean in its simplest form, "something that constitutes the place or point from which something else originates, takes form, or develops", which is what I was referring to, because it is meant as a source to develop other work, and not the mathematical term.

    Nevertheless, thank you for your feedback.

Skip to main content