Month: April 2013

Get Sql Server Log File Free Space via Powershell

Quick script (I think I start off all my posts with those two words) on how to get the free space of your transaction log file (along with other information about the files) with powershell.  Useful if you manually grow your log files (like we all should do, but most probably don’t [myself included]).  As always, this uses SQLPSX.  One of these days I’ll convert to SQLPS.

param(
	[string[]]$servers = @('ServerName')
)

Import-Module SqlServer

$outputs = @();

$servers | %{
	$serverName = $_
	Get-SqlDatabase -sqlserver $(Get-SqlServer -sqlserver $_ -username 'username' -password 'password') | %{
		$db = $_
		Get-SqlLogFile -database $_ | %{
			$output = New-Object -TypeName PSObject -Property @{
				ServerName = $serverName
				DatabaseName = $db.Name
				FileLogicalName = $_.Name
				FileLocation = $_.FileName
				FileGrowthKB = $_.Growth
				MaxSizeKB = $_.MaxSize
				AvailableSpace = ($_.Size - $_.UsedSpace)
				UsedSpace = $_.UsedSpace
				Size = $_.Size
				PercentFreeSpace = [Math]::Truncate((($_.Size - $_.UsedSpace) / $_.Size) * 100)
			}
			$outputs += $output	
		}
	}
}
$outputs | SELECT ServerName, DatabaseName, FileLogicalName, FileLocation, PercentFreeSpace, UsedSpace, AvailableSpace, Size  | sort PercentFreeSpace -Descending |  Format-Table -AutoSize

Find a Table with Powershell

Quick script to find which database a table resides in with powershell.  This assumes you have a file named servers.txt that lists the servers that you’ll be searching.  This uses SQLPSX as well.  Easy as cake.  

Import-Module SqlServer

$tableName = 'TableName'
$servers = Get-Content c:\Test\Servers.txt

$servers | %{
	$srv = $_
	Get-SqlDatabase -sqlserver $(Get-SqlServer -sqlserver $_ -username 'UserName' -password '') | where{-not $_.IsSystemObject} | %{
		$db = $_
		Get-SqlTable -Database $_ | where{$_.Name -eq $tableName} | %{
			write-host "$($srv) $($db.Name)"
		}
	}
}

Replace an XML node with Powershell

There may be a better way to do this (the xml spec defines a renameNode method, but as far as I can tell MS hasn’t implemented this, and if they have I couldn’t find it), but this sufficed for my purposes.  This just creates a new node under the parent of the node you’re looking for and then copies the original nodes’ text content into the new node.  It then removes the original node.  

[xml]$xmlDoc = Get-Content c:\XMLFile.xml

foreach($node in $xmlDoc.SelectNodes('/Node1/Node2')){
	$newNode = $xmlDoc.CreateNode([System.Xml.XmlNodeType]::Element, $node.Prefix, 'NewNode', $node.NamespaceURI);
	$newNode.InnerText = $node.InnerText;
	$node.ParentNode.AppendChild($newNode);
	$node.ParentNode.RemoveChild($node);
	
}
$xmlDoc.Save('c:\XMLFileReplaced.xml');

Export RDL Files from ReportServer Database with PowerShell

Quick (and long overdue) post on how to extract rdl files directly from the ReportServer database with powershell.

We have an old sql 2k box here that has a bunch of reports on it, and for some reason I’m not able to browse said reports.  I needed to get some of these reports out of this database and sent off to a client, but no matter what permissions I assigned in IIS, I still could not see the reports.  (I don’t give a tinker’s cuss about reporting in general).

This code (which uses SQLPSX, but no reason you couldn’t use native smo) extracts the reports from the dbo.Catalog table and writes them directly to file.  Easy peasy.

Import-Module SqlServer

$rows = Get-SqlData $(Get-SqlServer -sqlserver 'ServerName' -username 'LoginName' -password 'Password') `
	-dbname 'ReportServer' -qry 'SELECT * FROM dbo.Catalog WHERE Content IS NOT NULL'

foreach($row in $rows){
	$row.Content | Set-Content -Path "c:\Test\Reports\$($row.Name).rdl" -Encoding Byte
}

Keep in mind, all these reports were uniquely named.  If you have any reports that are named the same under different directories, you’ll need to implement code that will handle that.

Update 20130415

My good friend Will asked how to create the directories when doing an export from the Path column in the dbo.Catalog table.  Here is how you would accomplish this:

Import-Module SqlServer
cls

$destDir = 'c:\Test'

#if you're using windows auth, you can just pass in the server name in the -sqlserver
#instead of having to use the Get-SqlServer cmdlet.  This uses sqlpsx.
$rows = Get-SqlData $(Get-SqlServer -sqlserver 'ServerName' -username 'LoginName' -password 'Password!') -dbname 'ReportServer' -qry 'SELECT * FROM dbo.Catalog WHERE Content IS NOT NULL'

foreach($row in $rows){
	$newDir = $row.Path.ToString() -replace '/', '\'
	#new-item will automagically create the directory from the string variable
	#for example, string "c:\test\dir1\dir2" will create all three directories
	New-Item -ItemType Directory -Path "$($destDir)$($newDir)" -ErrorAction SilentlyContinue
	$row.Content | Set-Content -Path "$($destDir)\$($newDir)\$($row.Name).rdl" -Encoding Byte
}