Sunday, September 19, 2010

Enumerate SharePoint 2010 Databases

If you are working with SharePoint in a lab environment and use one shared database server to store databases from multiple farms then with SharePoint 2010 you can quickly lose control of your databases since there are much more of them now. When you set up a lab farm you typically run Products Configuration Wizard which leaves you with several databases created whose names contain GUIDs, which makes it hard to distinguish between them and relate them to corresponding farms.

Let’s say you need to get rid of a farm and want to delete corresponding databases after. What do you do? If you haven’t yet disconnected from the farm and uninstalled SharePoint then PowerShell comes at help. Log in to one of the farm servers, start up SharePoint 2010 Management Shell and enter this command:

Get-SPDatabase | % {$_.Name}

It will list names of all databases utilized in the current farm. You can now create a script to drop these databases or do whatever you needed to do with them. If however it is too late and you have uninstalled your servers, you can still get the list of database names using a SQL query:

USE SharePoint_Config
select distinct name from Objects WITH(NOLOCK) where Name in (
select Name COLLATE Latin1_General_CI_AS_KS_WS from sys.databases WITH(NOLOCK))
GO

Replace SharePoint_Config with the name of configuration database for your farm. SharePoint products use Latin1_General_CI_AS_KS_WS collation so you need to do a cast. It is of course not supported to issue queries directly against SQL Server so I would use this approach for development environment only, and only when you are really cleaning it up and PowerShell isn’t an option.

Alternatively to avoid the mess in your database names from the start you can also pre-create them. Here is the guidance. Lastly, although the guidance does not suggest sharing database role between farms in production environment, it is quite practical to share a SQL server in development environment if you are not using your farm for performance and capacity testing.

UPDATE: on technet there is a list of databases used by SharePoint (a section in storage and SQL Server capacity planning article): http://technet.microsoft.com/en-us/library/cc298801.aspx#section1a