Using PowerShell to produce a list of databases from a list of server\instances

Need to produce a report showing all the databases in your environment? Why not include the name, size and owner while we are at it and export it to a csv file. Here you go!

Note: The SQLPS module is installed on a machine with Microsoft SQL Server Management Studio. I have tested this with MSSMS 2014.

The PowerShell Script

import-module "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS" -DisableNameChecking
$rootdir = "C:\Users\VirtualRick\SQL Server Audit\"
$instances = import-csv $rootdir\server-instance.csv
ForEach($row in $instances)
{
$sqlPath = "SQLSERVER:\SQL\$($row.server)\$($row.instance)\Databases\"
dir $sqlPath | select Name, Size, Owner | export-csv $rootdir\export.csv -Append
}

 

server-instance.csv file example:

Server,Instance

MyServer,default

Upload local file to Azure File Share via Power Shell

Want to upload a log file to your cloud storage? Here is a quick and easy way to do it via Power Shell.


$AccountName = "YOURACCOUNTNAMEHERE"
$AccountKey = "YOURACCESSKEYHERE"
$Context01 = New-AzureStorageContext -StorageAccountKey $AccountKey -StorageAccountName $AccountName
Set-AzureStorageFileContent -ShareName backups -Source "C:\file.log" -Path "DestinationFile.Name" -Context $Context01

PowerShell script to compare the contents of two folders

Need to find out if two folders have the same files in them? Here is a quick PowerShell script to make it happen!

PS C:\Windows\system32> $server1 = get-childitem -Recurse -path E:\folder1
PS C:\Windows\system32> $server2 = get-childitem -Recurse -path \\server2\folder1
PS C:\Windows\system32> Compare-Object -ReferenceObject $server1 -DifferenceObject $server2

InputObject SideIndicator
———– ————-
Sample.file.txt <=

 

References:

https://blogs.technet.com/b/heyscriptingguy/archive/2011/10/08/easily-compare-two-folders-by-using-powershell.aspx

Obtain users DN via PowerShell

Note to self… ┬áThis is how you can get the correct DN, and other information, for a user in AD.

From PowerShell

import-module activedirectory

get-aduser -filter {EmailAddress -like “rick@virtualrick.*”}

or

get-aduser -filter {samAccountName -like “rick*”}