script to get list of sharepoint dbs from particular DB server pertaining to SharePoint server

Question

script to get list of sharepoint dbs along with its sizes, web application name and site count from particular DB server pertaining to SharePoint server
is it possible,? if yes how please share me script with which I should be generate to excel.

Thank you.

Answer 1

Hi @sns ,

Get Web Application And Site Collection Details of the specific Content DB using the following PowerShell script.

If ((Get-PSSnapin | where {$_.Name -match "SharePoint.PowerShell"}) -eq $null)    
{    
    Add-PSSnapin Microsoft.SharePoint.PowerShell    
}    
Clear-Host    
    
    
$ServerName = "SQL" #Bring your Server Name    
$webapps = Get-SPWebApplication    
$Output = "C:\$ServerName.csv" #Output file    
     
    
$SiteDataCollection = @()  # Array to store data    
foreach ($webapp in $webapps){    
    
$webappurl = $webapp.URL;    
$webappname=$webapp.DisplayName    
$sites=get-spsite -limit all -WebApplication $webapp.URL    
    
foreach ($site in $sites)    
{    
#enter Content DB name  
if($ContentDB.Name -eq "WSS_Content"){  
  
$ContentDB = Get-SPContentDatabase -site $site.url    
    
            $SiteData = New-Object PSObject     
            $SiteData | Add-Member -type NoteProperty -name "Web Application URL" -value $webappurl    
            $SiteData | Add-Member -type NoteProperty -name "Web Application Name" -value $webappname  
            $SiteData | Add-Member -type NoteProperty -name "Site Count" -value $($ContentDB.CurrentSiteCount)     
            $SiteData | Add-Member -type NoteProperty -name "Site URL" -value $site.url        
            $SiteData | Add-Member -type NoteProperty -name "Database Size" -value $($ContentDB.DiskSizeRequired /1024/1024)    
                           
            $SiteDataCollection += $SiteData    
    
}   
}   
}   
    
$SiteDataCollection | Export-Csv -Path $Output -Force    
    
Write-Host "Successfully Completed" -ForegroundColor DarkRed   

Thanks,
Echo Du

=====================
Updated Answer =======================

Hi @sns ,

Yes, if enter just Server name, you can get Web Application and Site Collection details of the Content DBs.

If ((Get-PSSnapin | where {$_.Name -match "SharePoint.PowerShell"}) -eq $null)    
{    
    Add-PSSnapin Microsoft.SharePoint.PowerShell    
}    
Clear-Host    
    
    
$ServerName = "SQL" #Bring your Server Name    
$webapps = Get-SPWebApplication    
$Output = "C:\$ServerName.csv" #Output file    
     
    
$SiteDataCollection = @()  # Array to store data    
foreach ($webapp in $webapps){    
    
$webappurl = $webapp.URL;    
$webappname=$webapp.DisplayName    
$sites=get-spsite -limit all -WebApplication $webapp.URL    
    
foreach ($site in $sites)    
{    
$ContentDB = Get-SPContentDatabase -site $site.url    
    
            $SiteData = New-Object PSObject     
            $SiteData | Add-Member -type NoteProperty -name "Web Application URL" -value $webappurl    
            $SiteData | Add-Member -type NoteProperty -name "Web Application Name" -value $webappname  
            $SiteData | Add-Member -type NoteProperty -name "Site Count" -value $($ContentDB.CurrentSiteCount)     
            $SiteData | Add-Member -type NoteProperty -name "Site URL" -value $site.url       
            $SiteData | Add-Member -type NoteProperty -name "Database Name" -value $ContentDB.Name     
            $SiteData | Add-Member -type NoteProperty -name "Database Size" -value $($ContentDB.DiskSizeRequired /1024/1024)    
            #$SiteData | Add-Member -type NoteProperty -name "Date Created" -value $site.RootWeb.Created      
            #$SiteData | Add-Member -type NoteProperty -name "OWner" -value $site.Owner          
                
            $SiteDataCollection += $SiteData    
  
}    
}    
    
$SiteDataCollection | Export-Csv -Path $Output -Force    
    
Write-Host "Successfully Completed" -ForegroundColor DarkRed  

Thanks,
Echo Du

======================
Updated Answer ============================

Hi @sns ,

 If ((Get-PSSnapin | where {$_.Name -match "SharePoint.PowerShell"}) -eq $null)    
 {    
     Add-PSSnapin Microsoft.SharePoint.PowerShell    
 }    
  
 Clear-Host    
             
 $ServerName = "SQL" #Bring your Server Name    
 $webapps = Get-SPWebApplication    
 $Output = "C:\$ServerName.csv" #Output file    
              
 $SiteDataCollection = @()  # Array to store data    
 foreach ($webapp in $webapps){    
        
 $webappurl = $webapp.URL;    
 $webappname=$webapp.DisplayName    
 $sites=get-spsite -limit all -WebApplication $webapp.URL    
        
 foreach ($site in $sites)    
 {    
 $SQLNames = Get-SPServer  
 $SQLNames  
 foreach ($SQL in $SQLNames){    
 if($SQL.Name -eq $ServerName){   
 $ContentDB = Get-SPContentDatabase -site $site.url    
        
             $SiteData = New-Object PSObject     
             $SiteData | Add-Member -type NoteProperty -name "Web Application URL" -value $webappurl    
             $SiteData | Add-Member -type NoteProperty -name "Web Application Name" -value $webappname  
             $SiteData | Add-Member -type NoteProperty -name "Site Count" -value $($ContentDB.CurrentSiteCount)     
             $SiteData | Add-Member -type NoteProperty -name "Site URL" -value $site.url       
             $SiteData | Add-Member -type NoteProperty -name "Database Name" -value $ContentDB.Name     
             $SiteData | Add-Member -type NoteProperty -name "Database Size" -value $($ContentDB.DiskSizeRequired /1024/1024)    
             #$SiteData | Add-Member -type NoteProperty -name "Date Created" -value $site.RootWeb.Created      
             #$SiteData | Add-Member -type NoteProperty -name "OWner" -value $site.Owner          
                    
             $SiteDataCollection += $SiteData    
      
 }  
 }  
 }    
 }    
        
 $SiteDataCollection | Export-Csv -Path $Output -Force    
        
 Write-Host "Successfully Completed" -ForegroundColor DarkRed  

Thanks,
Echo Du

----------------------------------
Updated Answer -----------------------------------
Hi @sns ,

Please try to the following PowerShell script

Add-PSSnapin Microsoft.SharePoint.PowerShell    
  
#Bring your Server Name        
$ServerName = "SQL"    
$webapps = Get-SPWebApplication    
$Output = "C:\$ServerName.csv"     
  
#Array to store data           
$SiteDataCollection = @()    
  
foreach ($webapp in $webapps)  
{    
    $webappurl = $webapp.URL;    
    $webappname=$webapp.DisplayName    
    $sites=get-spsite -limit all -WebApplication $webapp.URL    
        
foreach ($site in $sites)    
{    
    $ContentDB = Get-SPContentDatabase -site $site.url   
  
    if($ContentDB.Server -eq "SQL"){  
  
             $SiteData = New-Object PSObject     
             $SiteData | Add-Member -type NoteProperty -name "Web Application URL" -value $webappurl    
             $SiteData | Add-Member -type NoteProperty -name "Web Application Name" -value $webappname  
             $SiteData | Add-Member -type NoteProperty -name "Site Count" -value $($ContentDB.CurrentSiteCount)     
             $SiteData | Add-Member -type NoteProperty -name "Site URL" -value $site.url       
             $SiteData | Add-Member -type NoteProperty -name "Database Name" -value $ContentDB.Name     
             $SiteData | Add-Member -type NoteProperty -name "Database Size" -value $($ContentDB.DiskSizeRequired /1024/1024)          
                    
             $SiteDataCollection += $SiteData    
  
             }      
    }    
}    
        
$SiteDataCollection | Export-Csv -Path $Output -Force    
        
Write-Host "Successfully Completed" -ForegroundColor DarkRed  

Thanks,
Echo Du

===============

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.