How to list the sharepoint 2010 databases that are in use – eventually to a file ?

It happens to me all the time when I develop and test or after migrations attempts !

Well, I hope I’m not the only one who has ended with a lots of used and unused Sharepoint databases after numerous farm installs and reconfigs…

When time is in for a clean up of you SQL server databases you need to find out which databases your Sharepoint farm really uses. For that there is the Sharepoint 2010 management PowerShell cmdlet :

Get-SPDatabase | Sort-Object Name | Select Name

Which will nicely give you all the databases that Sharepoint uses on the SQL Server. It will sort them the same way that SQL Server Management Studio does

 

Sometimes you want to save the output to a file. Then use Out-File parameter. Like that :

Get-SPDatabase | Sort-Object Name | Select Name | Out-File c:tempSharepointDBsInUseList.txt

This way you can get an overview of which databases to delete/detach.  A way to enhance that might be to build a script to generate the SQL statement that you need to manage the databases on the server.

For exemple this script builds a script that returns all the databases where the name doesn’t match the name from the Sharepoint databases.

$file = New-Item -type file "c:tempSQLStatement.txt" -force
add-content $file "select name from sys.databases where name not in ("
Get-SPDatabase | foreach-object{add-content $file $_.Name} 
add-content $file ")"
ii $file

You need to do a bit of formating and add the missing quotes and the missing comas and you’re set !

With that in mind you’ll need new excuses for keeping the SQL server messy !!

Happy PowerShelling….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: