question

sns1 avatar image
0 Votes"
sns1 asked ·

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

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.

office-sharepoint-server-administration
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoDu-MSFT avatar image
0 Votes"
EchoDu-MSFT answered ·

Hi @sns1 ,

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 

68426-1.png


Thanks,
Echo Du
===================== Updated Answer =======================

Hi @sns1 ,

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

68893-sql.png

Thanks,
Echo Du
====================== Updated Answer ============================

Hi @sns1 ,

  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 @sns1 ,

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.






1.png (70.9 KiB)
sql.png (39.7 KiB)
· 17 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Echo Du,

thank you

output is coming 0 KB.

and If enter just SQL server name , I want to get a list of DBS with their sizes along with WA, SC count. is it possible?

0 Votes 0 ·

Hi @sns1 ,

I have updated my answer, Hope this can help you to solve issue.

Have a nice day!

Thanks,
Echo Du

0 Votes 0 ·
sns1 avatar image sns1 EchoDu-MSFT ·

HI Echo Du,

Thank you. It worked but giving list from different db servers also, is it possible to get list of dbs from that respective SQL server when I give SQL server name in the script?

is that db size in MB or GB?
and for one DB it is showing 29 GB from Database server, where in our script output it is showing 27140 GB
why difference?

0 Votes 0 ·
Show more comments