[2010-09-30 Updated with new featuers and download location]
It’s time for another PowerShell script. This one I wrote in a project where I needed to know what content databases was used by each farm. We had a large SQL cluster and more then one farm was using it. Since there is no enum content databases in stsadm i made this simple PowerShell script.
What it does basically is that it connects to the SPWebService and for each web application it find it lists all SharePoint Content Databases and writes it to either a xml or csv file named ContentDatabases in the folder where you run the script.
This is how you use it:
.SP-EnumContentDBs -OutPutFormat xml | csv
And now in the first version you will get the following information:
Web Application name
Content Database Name
Content Database ID(GUID)
Current amount of sites in DB
Maximum site count allowed in DB
Warning site level
Read only state
Database Server
But it’s easy to add more attributes by just adding more properties to the code. You can find all available properties at msdn: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spcontentdatabase_properties.
If you want to make a comparison to what you have on the SQL server you can easily run the script, import it to e.g. Excel and then run the following on the SQL server to get the list of databases:
select name from master..sysdatabases where dbid>4 order by name
I have started to move some of my PowerShell scripts to the codeplex project SharePoint Management PowerShell scripts This way I hope more people will find it usefull and others can participate in improving them. This script can be found here: http://sharepointpsscripts.codeplex.com/releases/view/53114
You can download the script by clicking here or it’s available at the download section.
Finally thanks to Travis Lingenfelder who with his blog post helped me with some strugeling getting the names out since it differs some from c#.
Love the function. I keep all my functions in a profile.ps1 file for quick access. I made a slight modification so that the results are entered into a datatable. From there I can bulk upload to SQL table, write to various file formats, etc. I hope you find this useful and thank you for your example.
function EnumContentDBs() {
$WebService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$dt = New-Object Data.datatable
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “WebApplication”; $dt.Columns.Add($Col)
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “DBName”; $dt.Columns.Add($Col)
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “DBID”; $dt.Columns.Add($Col)
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “Sites”; $dt.Columns.Add($Col)
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “MaxSites”; $dt.Columns.Add($Col)
$Col = New-Object Data.DataColumn; $Col.DataType = [String]; $Col.ColumnName = “ReadOnly”; $dt.Columns.Add($Col)
# “Web Application ; Database Name ; Database ID ; Sites in Database ; Maximum amount of sites in DB; Read only” | out-file $OutPutFile -append
foreach($WebApplication in $WebService.WebApplications){
$ContentDBCollection = $WebApplication.ContentDatabases
$webAppName = $WebApplication.name
$DBName = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty(“Name”)
$DBID = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty(“ID”)
$DBCurrentSiteCount = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty(“CurrentSiteCount”)
$DBMaximumSiteCount = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty(“MaximumSiteCount”)
$DBIsReadOnly = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty(“IsReadOnly”)
foreach($ContentDB in $ContentDBCollection){
$dr = $dt.NewRow()
$CurrentDBName = $DBName.GetValue($ContentDB, $null)
$CurrentDBID = $DBID.GetValue($ContentDB, $null)
$CurrentDBCurrentSiteCount = $DBCurrentSiteCount.GetValue($ContentDB, $null)
$CurrentDBDBMaximumSiteCount = $DBMaximumSiteCount.GetValue($ContentDB, $null)
$CurrentDBIsReadOnly = $DBIsReadOnly.GetValue($ContentDB, $null)
$dr["WebApplication"] = $webAppName
$dr["DBName"] = $CurrentDBName
$dr["DBID"] = $CurrentDBID
$dr["Sites"] = $CurrentDBCurrentSiteCount
$dr["MaxSites"] = $CurrentDBDBMaximumSiteCount
$dr["ReadOnly"] = $CurrentDBIsReadOnly
$dt.Rows.Add($dr)
# “$webAppName ; $CurrentDBName ; $CurrentDBID ; $CurrentDBCurrentSiteCount ; $CurrentDBDBMaximumSiteCount ; $CurrentDBIsReadOnly” | out-file $OutPutFile -append
}
}
Write-Output $dt
}
Thank you very much James. Good Work!
[...] original blog post about this is found here: http://mysharepointofview.com/2010/01/enumerate-all-content-databases-using-powershell/ 0 [...]