I got an email from an collegue who recently took over a SharePoint 2010 farm. He dug into the Central Admin and SQL and found he has a lot of repeated databases and databases with GUID naming that are legacy.
He was looking for a way to get a report just a idea of what were the active DB’s so he could archive and remove the un-used.
It is important to also remember once you have an active list, try and maintain this list in an administration list or report. You don’t want to do this on a regular schedule as it can take some time.
I like to reference these tried and true methods you can use to find not only the active databases used in your SharePoint environment but also find the properties for each.
—-
If you only need a list of all content databases, use SharePoint Central Administration.
In the Application Management section just click Manage content databases to go to a page that lists content databases used in your farms.
To see the complete list of all databases on a SQL Server instance or in a farm, use Microsoft SQL Server Management Studio.
This is a good way to find the databases but isn’t always feasible for one reason or another. Since SQL Server Management Studio lists all databases, it can be hard to out which ones are the SharePoint Server databases.
See Database types and descriptions for a complete list of the names of all the databases supported by SharePoint Products.
Download the Databases That Support SharePoint 2010 Products poster.
View the Databases That Support SharePoint 2010 Products poster online.
See other published SharePoint Server 2010 posters access Technical diagrams (SharePoint Server 2010).
If you want a report of all of your SharePoint databases that includes the GUIDs and related property values, use the SharePoint 2010 Management Shell.
There are several Windows PowerShell cmdlets you can use to find all of the SharePoint databases and then print this report to a text file. The quickest and perhaps easiest cmdlet is “Get-SPDatabase”. Run this cmdlet in the SharePoint 2010 Management Shell to list all of the SharePoint Server databases with properties for each one. From this potentially large list you can then obtain specific information such as the database ID by using additional syntax in your cmdlet. Similarly, also in the SharePoint 2010 Management Shell, run “Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name” and you will get a simple list of the names for each database. You can then print this list to a text file by adding, “| out-file c:\db.txt” to the end of the command. For detailed information, see Windows PowerShell for SharePoint Server 2010, Database cmdlets, Get-SPDatabase, and Get-SPContentDatabase.
A unique way to find all databases used by a SharePoint 2010 farm is often overlooked mainly because it provides a backup of your farm.
In Central Administration, in the Backup and Restore section, access Perform a backup. This page lists all of the items that you can backup in your farm. In this list are all of the databases used by SharePoint Server. Just expand all of the components and then look through the Type column to find the SharePoint database names. Of course, if you do not want to perform a backup, just click Cancel after you’ve listed all of the databases in your SharePoint farm.
Credit for some of these tips goes to where I discovered them, in the SharePoint 2010 – General Questions and Answers forum.
——I would like to thank Steve Hord for putting together the linking.