I have 2 on-premises farms 2013 & 2016. and i want to get all the sharepoint databases names,if they need updates and when they have been backup-ed, now i find this script to get the name and NeedsUpgrade:-

Get-SPDatabase | select Name,NeedsUpgrade

but i am not sure if i can get the last backup date for each database, using the Get-SPDatabase command?


I'm sure there are other, more optimal, solutions but this gets the job done:

$spDbs = Get-SPDatabase
$db = $spDbs[0]

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $db.ServerName

$dbs = $s.Databases

#Retrieves the last backup dates - both for FULL and LOG backups 
$outItems = New-Object System.Collections.Generic.List[System.Object]
ForEach($sqlDb in $dbs){
    ForEach($spDb in $spDbs){
        if($sqlDb.Name -eq $spDb.Name){

$outItems | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize
| improve this answer | |
  • thanks for the script, i tried it inside our test environment SP 2013 and i got the info i were searching for.. but i always get worried when running suhc scripts on live environments,, so can you confirm if running your script on live environment is supported and does not have any drawbacks? thnaks – SharePoint TestDev Apr 9 '19 at 12:04
  • @SharePointTestDev I don't see any reason why it shouldn't be supported on production environment. As for drawbacks, probably there are better solutions than two nested foreach loops but I wouldn't be worried about performance if that's your concern. Since the script doesn't call any other cmdlets which modify data, I would say it is safe. – Slaven Semper Apr 10 '19 at 6:44
  • now when i run this on our test environment which have both SQL & SP installed on the same server your script worked well, but when i tried to run it inside live sharepoint environment, which have the sql on different server i got this error New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type is loaded. – SharePoint TestDev Apr 10 '19 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.