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….
The Blobfarm 