One of the best ways to know what databases your SharePoint deployment uses is to keep a record and add database names each time you create a new database.
This isn’t always easy as there usually isn’t enough extra time during the day to keep records. Plus, more often than not your SharePoint database maintenance tasks tend to occur either late at night or in the pre-dawn hours when no users are accessing the system, so remembering to add a new database name to an ongoing list is really tough.

Luckily, there are several 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.

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 cmdletsGet-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.

Thanks for reading,

Steve Hord, Technical Writer, SharePoint Content Publishing