Programmatically find MS Shipped Databases

[アーティクル]
10/27/2013

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	https://support.microsoft.com/kb/317604 https://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	https://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	https://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	https://blogs.msdn.com/b/skesiraju/archive/2009/02/03/indentify-biztalk-version-and-edition.aspx https://msdn.microsoft.com/en-us/library/aa578342.aspx https://msdn.microsoft.com/en-us/library/aa578342(v=bts.10).aspx https://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	https://msdn.microsoft.com/en-us/library/aa624918(v=ax.50).aspx
Dynamics CRM 2011	MSCRM_Config	Organization; OrganizationProperties; Notification	https://msdn.microsoft.com/en-us/library/cc308176.aspx
Dynamics CRM 4.0 / 2011	<variable>_MSCRM	ContactBase; ContactExtensionBase; AccountBase; AccountExtensionBase;	https://msdn.microsoft.com/en-us/library/cc308176.aspx https://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;	https://support.microsoft.com/kb/850662 https://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	https://support.microsoft.com/kb/980653 https://www.microsoft.com/en-us/download/details.aspx?id=24432
FAST Search Server 2010 for SharePoint	FASTSearchAdminDatabase		https://technet.microsoft.com/library/cc678868(office.14).aspx
Forefront Endpoint Protection 2010	FEPDW FEPDB_<variable>		https://technet.microsoft.com/en-us/library/gg710933.aspx
Forefront Identity Management 2010	FIMService		https://technet.microsoft.com/en-us/library/hh322891(v=ws.10)
Lync 2010	xdsrtcrtcdynrtcabrtcab1cpsdynrgsdynrgsconfiglisLcsLogLcsCDRQoEMetricsNo specific name for Group Chat database as it varies based on customer preference	FocusJoinsAndLeaves;MediationServers;ConferenceSessionDetails;AppliedBandwidthSource;PayloadDescription;PrincipalMemberDifference;PrincipalAffiliations	https://technet.microsoft.com/en-us/library/gg398370(v=ocs.14).aspx
Lync 2013	xdsrtcrtcsharedrtcxdsrtcdynrtcabrtcab1cpsdynrgsdynrgsconfiglislyssLcsLogLcsCDRQoEMetrics No specific name for Persistent Chat database as it varies based on customer preference	FocusJoinsAndLeaves;MediationServers;ConferenceSessionDetails;AppliedBandwidthSource;PayloadDescription;tblPrincipalMemberDifference;tblPrincipalAffiliations	https://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>		https://technet.microsoft.com/library/cc678868(office.14).aspx
PowerPivot	DefaultPowerPivotServiceApplicationDB_<variable>		https://technet.microsoft.com/library/cc678868(office.14).aspx https://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2
Project Server 2010	ProjectServer_Draft ProjectServer_Published ProjectServer_Archive_<variable> ProjectServer_Reporting		https://technet.microsoft.com/library/cc678868(office.14).aspx
Project Server 2013	ProjectWebApp (Default - can be changed)		https://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2
SC Orchestrator 2012	Orchestrator	MaintenanceTasks; TASK_RUNPROGRAM
SCCM 2007	SMS_<variable>	Agents; DistributionPoints; DistributionPointGroup; SysResList	https://www.microsoft.com/en-us/download/details.aspx?id=22052
SCCM 2012	CM_<variable>	Agents; DistributionPoints; DistributionPointGroup; SysResList	https://msdn.microsoft.com/en-us/library/hh948178.aspx https://connect.microsoft.com/SQLServer/feedback/details/783327/trace-flag-8295
SCCM 2012	SUSDB
SCOM 2007R2 / 2012 / 2012R2	OperationsManager OperationsManagerDWOperationsManagerAC	ManagedEntity; MaintenanceMode	https://technet.microsoft.com/en-us/library/gg508713.aspx
SCSM 2010	ServiceManagerDWRepositoryDWStagingAndConfigDWRepositoryDWDataMart		https://technet.microsoft.com/en-us/library/ff461229.aspx
SCSM 2012	Service ManagerDWStagingAndConfigDWRepositoryDWDataMartDWASDataBaseOMDWDataMartCMDWDataMart		https://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	https://technet.microsoft.com/en-US/library/cc678868(v=office.12).aspx
Sharepoint 2007	SSO		https://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	https://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	https://technet.microsoft.com/en-us/library/cc678868.aspx
SQL 2012 Semantic Search	Semanticsdb		https://www.microsoft.com/en-us/download/details.aspx?id=29069
SSRS	ReportServer	ChunkData; ExecutionLogStorage; Subscriptions; SegmentedChunk	https://technet.microsoft.com/en-us/library/ms159093.aspx
SSRS	ReportServerTempDB	ChunkData; ExecutionCache; SegmentedChunk	https://technet.microsoft.com/en-us/library/ms159093.aspx
SSRS (Sharepoint Integrated Mode)	ReportingService_<variable> ReportingService_<variable>_Alerting RSDB	ChunkData; ExecutionLogStorage; Subscriptions; SegmentedChunk	https://technet.microsoft.com/en-us/library/ms157285.aspx https://technet.microsoft.com/library/cc678868(office.14).aspx#Section7 https://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4
SSRS (Sharepoint Integrated Mode)	ReportingService_<variable>TempDB RSTempDB	ChunkData; ExecutionCache; SegmentedChunk	https://technet.microsoft.com/en-us/library/ms157285.aspx https://technet.microsoft.com/library/cc678868(office.14).aspx#Section7 https://technet.microsoft.com/en-US/library/cc678868(v=office.15).aspx#Sec4
TFS2010 / TFS 2012	Tfs_Configuration	tbl_AccessMapping; tbl_CatalogResource; tbl_NotificationQueue	https://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB
TFS2010 / TFS 2012	Tfs_DefaultCollectionTfs_<variable>	FenceConfiguration; FencedLabSystemConfiguration; WorkItemsAre; WorkItemsWere	https://msdn.microsoft.com/en-us/library/ms253070.aspx#TableDB
TFS2010 / TFS 2012	Tfs_Warehouse	FactWorkItemLinkHistory; DimToolArtifactDisplayUrl; FactWorkItemHistory	https://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

Anonymous
October 28, 2013
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. :-)
Anonymous
November 08, 2013
Most useful. Thanks.N.B. however that it's a table. Tables contain heterogeneous elements, matrices contain homogeneous elements.
Anonymous
November 08, 2013
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.

次の方法で共有

Programmatically find MS Shipped Databases

Comments

その他のリソース