Saturday, 2 March 2013

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
 
1:  # Script to find Database last backup and restore time on a given set of SQL server instances  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 15/02/2013  
5:  # Script Help :-  
6:  #---------------  
7:  # Parameter 1 :- "-s" to run powershell for Single Instance of SQL  
8:  # "-f" to give filename with list of SQL Servers(Please provide SQL server name with instance details like <servername\instancename>  
9:  # Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)  
10:  #  
11:  # Example1:- FindDBBkp.ps1 -s <SQLservername\instancename>  
12:  # Example2:- FindDBBkp.ps1 -f <filename with fully define path like c:\test.txt>  
13:  # 
 
14:  Clear-Host  
15:  #Write-Host "Num Args:" $args.Length;  
16:  $List =@()  
17:     if($args.Length -ne 2)  
18:     {  
19:        Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"  
20:     }  
21:     elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
22:     {  
23:        $computer = $args[1]  
24:        if($computer -ne $null)  
25:        {  
26:           $srt = "Server Name :- " + $computer  
27:           $srt  
28:           Echo "---------------------------"  
29:           [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null  
30:           $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
31:           $dbsall = $srv.Databases  
32:           #$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
33:           $dbs = $dbsall|Where {$_.IsAccessible}  
34:           $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 |  
35:               Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|  
36:                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"}  
37:                else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp  
38:           $dt = new-object "System.Data.DataTable"  
39:           $cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"  
40:           $cn.Open()  
41:           $sql = $cn.CreateCommand()  
42:           $sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"  
43:           $rdr = $sql.ExecuteReader()  
44:           $dt.Load($rdr)  
45:           $cn.Close()  
46:           $List = @()  
47:           $List1 = @()  
48:           Foreach($bkprcd in $bkp)  
49:           {  
50:           $dbnamechk = $bkprcd.name  
51:           $restrcr = $dt|where {$_.dbname -eq $dbnamechk}  
52:           $restrdt = $restrcr.restore_date  
53:           $restrby = $restrcr.RestoredBy  
54:           if($restrdt -eq $null){$restrdt = "No Restore"}  
55:           if($restrby -eq $null){$restrby = "No Restore"}  
56:           $List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name Restore_Date -Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby -PassThru  
57:           $List += $List1   
58:           }  
59:           $List|ft -AutoSize  
60:        }  
61:    }elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))  
62:     {  
63:        $filename = $args[1]  
64:        $computers = get-content $filename  
65:        $List = @()  
66:        foreach ($computer in $computers)  
67:        {  
68:        if($computer -ne $null)  
69:        {  
70:           $srt = "Server Name :- " + $computer  
71:           $srt  
72:           Echo "---------------------------"  
73:           "`n"  
74:           [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null  
75:           $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
76:           $dbsall = $srv.Databases  
77:           #$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
78:           $dbs = $dbsall|Where {$_.IsAccessible}  
79:           $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 |  
80:               Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|  
81:                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"}  
82:                else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp  
83:           $dt = new-object "System.Data.DataTable"  
84:           $cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"  
85:           $cn.Open()  
86:           $sql = $cn.CreateCommand()  
87:           $sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"  
88:           $rdr = $sql.ExecuteReader()  
89:           $dt.Load($rdr)  
90:           $cn.Close()  
91:           $List = @()  
92:           $List1 = @()  
93:           Foreach($bkprcd in $bkp)  
94:           {  
95:           $dbnamechk = $bkprcd.name  
96:           $restrcr = $dt|where {$_.dbname -eq $dbnamechk}  
97:           $restrdt = $restrcr.restore_date  
98:           $restrby = $restrcr.RestoredBy  
99:           if($restrdt -eq $null){$restrdt = "No Restore"}  
100:           if($restrby -eq $null){$restrby = "No Restore"}  
101:           $List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name LastRestore_Date-Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby-PassThru  
102:           $List += $List1   
103:           }  
104:           $List|ft -AutoSize  
105:           "`n"  
106:        }  
107:        }  
108:     }else  
109:  {  
110:  Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"  
111:  }  

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

1 comment:



  1. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Script to find backup time for all database of SQL Server.

    http://www.dbrnd.com/2015/10/sql-server-script-to-find-last-backup-time-for-all-databases/

    ReplyDelete