Listando informação de Backups – Powershell


 

Esta função retorna a informação de backups, as propriedades retornadas são :

 

LineNumber = Número da Linha autoincremento
Date = Data da coleta
ServerName = nome do servidor
DatabaseName = Nome do Database
LastBackupDate = Data Último Backup Full (yyyy/mm/dd hh:mm:ss)
LastDifferentialBackupDate = Data Último Backup Diferencial (yyyy/mm/dd hh:mm:ss)
LastLogBackupDate = Data Último Backup de Log (yyyy/mm/dd hh:mm:ss)

 

Da mesma maneira, a intenção é não engessar os comdlets. A partir das informações você apode aplicar filtros, exibir da maneira que quiser , inserir no SQL Server  usando os recursos do powershell.

O parâmetro mandatório a ser passado é um só. Ou você passa o caminho completo do txt que conterá o nome dos servidores ou deixa em branco que será pego somente o corrente.

Esta função gera um log para os problemas encontrados no path c:\temp (cria se não houver), ou se você quiser pode passar como parâmetro o caminho (não é mandatório)

  1: Function Save-SQLMsg () 
  2: <#
  3: ----------------------------------------------------------
  4: Save  log in file
  5: ----------------------------------------------------------
  6: Requires POWERSHELL 2.0
  7: 
  8: File Name     			= $NamePS1
  9: Server name   			= $Server 
 10: DatabaseName 			= $databasename
 11: Message To Log		    = $Message
 12: Path to generate file 	= $PathFileLog 
 13: Date to Log				= $TodayDate
 14: #>
 15: 
 16: 
 17: {
 18: 
 19: 	[CmdletBinding()]
 20: 	
 21: 	Param (
 22: 		[Parameter(position=1,Mandatory = $true )][String] $NamePS1,
 23: 		[Parameter(position=2,Mandatory = $true )][String] $Server,
 24: 		[Parameter(position=3,Mandatory = $false )][String] $DatabaseName = "",
 25: 		[Parameter(position=4,Mandatory = $false )][String] $Message = "",
 26: 		[Parameter(position=5,Mandatory = $false )][String] $PathFileLog = "C:\temp1",
 27: 		[Parameter(position=6,Mandatory = $false )][String] $TodayDate = (Get-Date -Format "yyyyMMddhhmmss")
 28: 		)
 29: 	process 
 30: 	{
 31: 	
 32: 		#test if path wich will contains the error file exists. if not create 
 33: 	
 34: 	if (!(Test-Path -path $PathFileLog))
 35: 	{
 36: 		try {
 37: 			New-Item $PathFileLog -itemtype directory -ErrorAction  Stop   | Out-Null
 38: 		}
 39: 		catch {
 40: 			Write-Host "Can not create log file path"
 41: 			break;
 42: 		}
 43: 	} 
 44: 	
 45: 	
 46: 	$NameFileFull = $PathFileLog + "\" + $NamePS1 + $TodayDate + ".log" 
 47: 	
 48: 	$TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2) + "-" + $TodayDate.Substring(6,2) 
 49: 	
 50: 	"Server : " + $Server + " Database : " + $DatabaseName + " Date : "  + $TDate + " Message: "  + $Message | Out-file  $NameFileFull -append 
 51: 	} 
 52: }
 53: 
 54: Function Get-SQLBackup () 
 55: {
 56: 
 57: <#
 58: ----------------------------------------------------------
 59: Get all backup information
 60: ----------------------------------------------------------
 61: Requires POWERSHELL 2.0
 62: 
 63: $TXTServersList         = Txt with servers to be checked - Default server
 64: 
 65: 
 66: #>
 67: 	[CmdletBinding()]
 68: 	
 69: 	PARAM	(
 70: 				[Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="File Servers List")]
 71: 				[Alias("FullNameTXT")]
 72: 				[String] $TXTServersList = $env:COMPUTERNAME
 73: 				
 74: 			)
 75: 
 76: 	begin 
 77: 	{
 78: 		[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
 79: 	}
 80: 	Process
 81: 	{
 82: 	
 83: 		$verbosePreference="continue" 
 84: 		[datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss"
 85: 		if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT")
 86: 		{
 87: 			try
 88: 			{
 89: 				$ServersList = get-content $TXTServersList	
 90: 			} catch {
 91: 						$msg = $error[0]
 92: 						Write-Warning $msg
 93: 						break;
 94: 			}
 95: 		}	
 96: 		else
 97: 		{
 98: 			$ServersList = $TXTServersList
 99: 		}	
100: 		
101: 		
102: 		$LineNumber = 1
103: 		$FinalResult = @()
104: 	
105: 		foreach ($svr in  $ServersList )
106: 		{
107: 			try 
108: 			{
109: 				$DatabaseName = ""
110: 				$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
111: 				$Data = $Server.Databases| where-object {$_.IsSystemObject -eq $FALSE -and
112: 				$_.IsAccessible -eq $TRUE } | foreach {
113: 
114: 		
115: 					$Object = New-Object PSObject
116: 
117: 					[datetime] $LastBackupDate 			= "{0:yyyy/MM/dd hh:mm:ss}"	-f  [datetime] $_.LastBackupDate  
118: 					[datetime]$LastDifferentialBackupDate = "{0:yyyy/MM/dd hh:mm:ss}" -f  [datetime] $_.LastDifferentialBackupDate  
119: 					[datetime]$LastLogBackupDate 			= "{0:yyyy/MM/dd hh:mm:ss}"	-f  [datetime] $_.LastLogBackupDate
120: 					[String] $DatabaseName = $_.name
121: 
122: 
123: 					$Object | add-member Noteproperty LineNumber  					$LineNumber 	
124: 					$Object | add-member Noteproperty Date  						$TodayDate 	
125: 					$Object | add-member Noteproperty ServerName  					$svr
126: 					$Object | add-member Noteproperty DatabaseName 					$DatabaseName 					
127: 					$Object | add-member Noteproperty LastBackupDate 				$LastBackupDate
128: 					$Object | add-member Noteproperty LastDifferentialBackupDate 	$LastDifferentialBackupDate
129: 					$Object | add-member Noteproperty LastLogBackupDate 			$LastLogBackupDate
130: 
131: 					$FinalResult += $Object
132: 					$LineNumber ++ 
133: 					
134: 				}                
135: 				Write-Output $FinalResult					
136: 			}   catch	{ 
137: 					$msg = $error[0]
138: 					Write-Warning $msg
139: 					Save-SQLMsg "Get-SQLBackup" "$svr" "$DatabaseName" "$msg" 
140: 					continue
141: 			} 
142: 		}	
143: 	}
144: }

Baixar Get-SQLbackup i

Exemplos de uso :

#list all backup information default server
Get-SQLBackup

#list all backup information using txt
Get-SQLBackup "C:\TEMP\Servers.txt"

# list all databases without backup full for 1 day
Get-SQLBackup "C:\TEMP\servers1.txt" | where-Object {( (get-Date) – ($_.LastBackupDate)).days -gt 1}  |
Select LineNumber,Date,ServerName,DatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate | Format-Table

# list all databases backup information and stored sql table
create table (BackupsXML XML)
(Get-SQLBackup "C:\TEMP\servers.txt" | Select LineNumber,Date,ServerName,DatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate | ConvertTo-Xml -NoTypeInformation).save("c:\temp\Backups.xml")
$XML = (Get-Content "C:\Temp\Backups.xml" ) -replace "’", "”"
$SQL = "insert into BackupsXML (XMLCol) values (‘$XML’)"
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql

# TSQL to list with servername  condition = "Jupiter"
Select t2.Col1.value(‘(Property[@Name="LineNumber"]/text())[1]’, ‘int’) LineNumber,
    t2.Col1.value(‘(Property[@Name="Date"]/text())[1]’, ‘nvarchar(255)’) Date,
    t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) ServerName,
    t2.Col1.value(‘(Property[@Name="DatabaseName"]/text())[1]’, ‘nvarchar(255)’) DatabaseName,
    t2.Col1.value(‘(Property[@Name="LastBackupDate"]/text())[1]’, ‘nvarchar(255)’) LastBackupDate
FROM dbo.BackupsXML
CROSS APPLY xmlcol.nodes(‘/Objects/Object’) As t2(Col1)
where t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) = ‘Jupiter’

POWERSHELL ROCKS !!!!

“Under the arc of a weather stain boards
Ancient goblins, and warlords
Come out of the ground, not making a sound
The smell of death is all around
And the night when the cold wind blows, no one cares, nobody knows

(2x)
I don’t want to be buried in a Pet Sematary
I don’t want to live my life again”

Pet Sematary

Ramones

Follow me on Twitter

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s