PowerShell Script to Find last Backup and Restore of Databases

The below script helps to find Database last backup and restore time on a given set of SQL server instances
 

	# Script to find Database last backup and restore time on a given set of SQL server instances
	# Created by - Vinoth N Manoharan
	# Version 1.1
	# Date - 15/02/2013
	# Script Help :-
	#---------------
	# Parameter 1 :- "-s" to run powershell for Single Instance of SQL
	# "-f" to give filename with list of SQL Servers(Please provide SQL server name with instance details like <servername\instancename>
	# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
	#
	# Example1:- FindDBBkp.ps1 -s <SQLservername\instancename>
	# Example2:- FindDBBkp.ps1 -f <filename with fully define path like c:\test.txt>
	#
	Clear-Host
	#Write-Host "Num Args:" $args.Length;
	$List =@()
	if($args.Length -ne 2)
	{
	Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"
	}
	elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))
	{
	$computer = $args[1]
	if($computer -ne $null)
	{
	$srt = "Server Name :- " + $computer
	$srt
	Echo "---------------------------"
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null
	$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"
	$dbsall = $srv.Databases
	#$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}
	$dbs = $dbsall|Where {$_.IsAccessible}
	$bkp = $dbs|Add-Member -MemberType ScriptProperty -Name FullBkp -Value{if($this.LastBackupDate -eq "01/01/0001 00:00:00"){"No FULL backup"}else{$this.LastBackupDate}}-PassThru |
	Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|
	Add-Member -MemberType ScriptProperty -Name LogBkp -Value{if($db.RecoveryModel.ToString() -eq "Simple"){"DB in Simple Recovery"}elseif($db.LastLogBackupDate -eq "01/01/0001 00:00:00"){"No Log backup"}
	else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp
	$dt = new-object "System.Data.DataTable"
	$cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"
	$cn.Open()
	$sql = $cn.CreateCommand()
	$sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"
	$rdr = $sql.ExecuteReader()
	$dt.Load($rdr)
	$cn.Close()
	$List = @()
	$List1 = @()
	Foreach($bkprcd in $bkp)
	{
	$dbnamechk = $bkprcd.name
	$restrcr = $dt|where {$_.dbname -eq $dbnamechk}
	$restrdt = $restrcr.restore_date
	$restrby = $restrcr.RestoredBy
	if($restrdt -eq $null){$restrdt = "No Restore"}
	if($restrby -eq $null){$restrby = "No Restore"}
	$List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name Restore_Date -Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby -PassThru
	$List += $List1
	}
	$List|ft -AutoSize
	}
	}elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))
	{
	$filename = $args[1]
	$computers = get-content $filename
	$List = @()
	foreach ($computer in $computers)
	{
	if($computer -ne $null)
	{
	$srt = "Server Name :- " + $computer
	$srt
	Echo "---------------------------"
	"`n"
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null
	$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"
	$dbsall = $srv.Databases
	#$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}
	$dbs = $dbsall|Where {$_.IsAccessible}
	$bkp = $dbs|Add-Member -MemberType ScriptProperty -Name FullBkp -Value{if($this.LastBackupDate -eq "01/01/0001 00:00:00"){"No FULL backup"}else{$this.LastBackupDate}}-PassThru |
	Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|
	Add-Member -MemberType ScriptProperty -Name LogBkp -Value{if($db.RecoveryModel.ToString() -eq "Simple"){"DB in Simple Recovery"}elseif($db.LastLogBackupDate -eq "01/01/0001 00:00:00"){"No Log backup"}
	else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp
	$dt = new-object "System.Data.DataTable"
	$cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"
	$cn.Open()
	$sql = $cn.CreateCommand()
	$sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"
	$rdr = $sql.ExecuteReader()
	$dt.Load($rdr)
	$cn.Close()
	$List = @()
	$List1 = @()
	Foreach($bkprcd in $bkp)
	{
	$dbnamechk = $bkprcd.name
	$restrcr = $dt|where {$_.dbname -eq $dbnamechk}
	$restrdt = $restrcr.restore_date
	$restrby = $restrcr.RestoredBy
	if($restrdt -eq $null){$restrdt = "No Restore"}
	if($restrby -eq $null){$restrby = "No Restore"}
	$List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name LastRestore_Date-Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby-PassThru
	$List += $List1
	}
	$List|ft -AutoSize
	"`n"
	}
	}
	}else
	{
	Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"
	}

view raw SQLTechnet -FindDBBackDate.ps1 hosted with ❤ by GitHub

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.