Retrieve Default SQL Server Backup Folder using PowerShell

As I've been translating a lot of my TSQL script to Windows PowerShell with SMO, here's another article I wrote on how to check for the default SQL Server backup folder

Connecting to SQL Server via Windows PowerShell with SQL Server authentication

Most of the articles I've written about SQL Server with Windows PowerShell have been using Windows Authentication. And while it is highly recommended to use Windows authentication to connect to SQL Server, the reality is that the IT infrastructures we have don't run on Microsoft Windows.

Here's an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication

Query Hyper-V Virtual machines using Windows PowerShell

Being a lazy administrator as I am, I try to minimize the amount of mouse-clicks I need to make to retrieve information about something on a Windows platform. As I have been using Microsoft Hyper-V on a bunch of my test machines, I always check if a VM is up and running before I power down my host machine (imagine the amount of electricity consumed just by keeping your machine up and running even without using it). This is specifically the case when dealing with my Windows XP VMs. I noticed that the profiles get corrupted if I shutdown the host machine without properly shutting down the VM. So, I always made sure that the VMs are not running before powering down the host machine.

I wrote a PowerShell command to query the current state of the VMs running on Hyper-V


Get-WMIObject -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "."

This will actually display a bunch of information about the VMs running on Hyper-V but what we're really concerned about is the name of the VM and it's currently running state. These two properties are associated with the ElementName and EnabledState attributes of the MSVM_ComputerSystem class. All we need to do with the command above is to pipe the results to a Select-Object cmdlet, specifying only these two properties, as follows

Get-WMIObject -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "." Select-Object ElementName, EnabledState

While the EnabledState property will give you a bunch of numbers, I'm only concerned with those values equal to 2, which means that the VM is running. But, then, you might not remember what the value 2 means. So might as well write an entire script that checks for the value of the EnabledState property. I've used the GWMI alias to call the Get-WMIObject cmdlet

$VMs = gwmi -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "."
foreach ($VM IN $VMs)
{
switch ($VM.EnabledState)
{
2{$state="Running" }
3{$state="Stopped" }
32768{$state="Paused" }
32769{$state="Suspended" }
32770 {$state="Starting" }
32771{$state="Taking Snapshot" }
32773{$state="Saving" }
32774{$state="Stopping" }
}
write-host $VM.ElementName `,` $state
}

On a side note, make sure you are running as Administrator when working with this script as you will only see the VMs that your currently logged in profile has permission to access. Running as Administrator will show you all of the VMs configured on your Hyper-V server

Creating Active Directory Users with Windows PowerShell

While it may seem easy to create Active Directory users using the management console, I still prefer doing it using scripts so as to make sure that they are done in a uniform, standard fashion (not to mention as fast as one can possibly do especially if you will be doing it for many users). I've referenced the scripts provided at the CodePlex site for ADSI and Active Directory for Windows PowerShell (full credit goes to them) to create users in Active Directory for Windows Server 2008. This also works for Windows Server 2003. While I may be a big fan of automation, it is important to highlight that processes are what makes automation really work. The reason I am saying this is that the CSV file can come from different sources, say, an intranet site where you ask employees to log in and key in their details. Having a process in place to make sure that users who would be entering their details in a standard way would eliminate the need to cleanse the data (I'm still thinking as a DBA here) in the long run. Plus, having a standard in place as an organization is starting out will make it flexible enough to scale as growth happens.


# define constants
$domainstr = ",dc=domainName,dc=local"
$domainnb = "domainName" # domain netbios name
$domain = "domainName.local"

$ADs_UF_NORMAL_ACCOUNT = 512 # Disables account and sets password required.

# Remember to enable the account before logging in


# Prompt user to enter the default passsword for the users
$defaultPassword = Read-Host "Please enter default Password:" -asSecureString

# Read the list of users from the CSV file
# Include other user properties in the CSV file as necessary

Import-csv users.txt | foreach
{
# Create user name based on FirstName and LastName column in the CSV file
$strUser = $_.firstName + " " + $_.lastName


#Form the LDAP string based on the OU column from the CSV file
$strLDAP = "LDAP://OU=" + $_.OU + ",OU=domainName Domain Users" + $domainstr

$target = [ADSI] $strLDAP
$newUser = $target.create("User", "cn=" + $strUser)
$newUser.SetInfo()

#Define a naming convention for the login based on your corporate policy
#This one uses the first letter of the firstname and the lastname
$userID = $_.firstName[0]+$_.lastName

#Define the other user attributes based on the columns defined in the CSV file
$newUser.sAMAccountName = $userID.ToString()
$newUser.givenName = $_.firstName
$newUser.sn = $_.lastName
$newUser.displayName = $_.firstName + " " + $_.lastName
$newUser.userPrincipalName = $_.firstName[0]+$_.lastName + "@" + $domain
$newUser.mail = $_.Email
$newUser.physicalDeliveryOfficeName = $_.Location
$newUser.title = $_.Designation
$newUser.description = $_.Designation
$newUser.SetInfo()

$newUser.SetPassword($defaultPassword.ToString())

#Normal user that requires password & is disabled
$newUser.userAccountControl = $ADs_UF_NORMAL_ACCOUNT

Write-Host "Created Account for: " $newUser.Displayname

}

Check the last backup date in SQL Server using WIndows PowerShell

This article highlights how to use Windows PowerShell to retrieve database properties using SMO. Notice how easy it is to check the database properties using pretty common syntax

One of the challenges I have when I was starting out as a SQL Server DBA was to check for the last backup date for a database. One way to do this is to find out which tables in the MSDB database contain the records of the backup history. What's really challenging here is the fact that you would have to look at the tables and their corresponding relationships which, apparently, MSDB doesn't have. You simply have to rely on what SQL Server Books Online has to say. Plus, the MSDB database will only contain records for databases with backups. What about those without?

For SQL Server 2005, the script below displays the last backup date of all the databases on your SQL Server instance. This script is from the MSDN Code Gallery

SELECT
T1.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') AS LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

You can simply replace the sys.sysdatabases table with master.dbo.sysdatabases for SQL Server 2000

Below is the equivalent script using Windows PowerShell.

$instance="Your_SQL_Server_Instance_Name"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases
$dbs | select Name,LastBackupDate, LastLogBackupDate | format-table -autosize

The only thing to note here are the last two lines - the one that creates an instance of the database object and the one that displays and formats a few of the database object properties. The first few lines will be the same for just about any PowerShell script that will access SQL Server using SMO

Are you a SQL Server DBA wanting to Learn Windows PowerShell?

I've been working on PowerShell for quite some time but mostly for systems administration tasks. Since Microsoft has decided to make PowerShell as a common engineering criteria for all server applications being released, every SQL Server DBA needs to know at least what it is and what they can do with it.

This article is a first in this series of introducing Windows PowerShell to SQL Server DBAs. Go check it out

Start Windows Services using PowerShell

I start and stop services on my workstation every now and then for testing purposes (well, primarily to stop services I do not use to minimize surface area and free up available resources which are not being used). What I used to do was to run a NET START command listing all the services that are started and piping it to a text file. Next, I'd insert a NET STOP on each line in the list to call the NET STOP command stopping that particular service. Unfortunately, there are cases where I wanted to start a specific service that start with a specific name. An example of this is starting the SQL Server instances running on my laptop. I have like 4 instances which are SQL Server versions ranging from 2000 to 20008. Now, I happen to memorize the instance names of those SQL Server instances, making it easy for me to just run a NET STOP servicename. Although it would be rather easy, imagine doing that on a server with like 9 instances (not to mention memorizing the instance names). I wouldn't want to type the NET STOP command 9 times. Enter PowerShell

Here's a PowerShell script to start all SQL Server instances on the local computer. You can modify the parameters if you want to suit your needs. I've used this to start all the services related to Symantec on my machine as well

Get-Service where {$_.Name -like "MSSQL$*"} Start-Service

(NOTE: There's a pipe character "" between Get-Service cmdlet and Where and before the Start-Service cmdlet. It just does not display here)

Another one-liner in Windows PowerShell

I did mention that I have been trying to convert my VBScript files to PowerShell scripts due to ease of coding. One of them happened to be a script that iterates a folder containing logs, reads the contents of the log files and appends them to a text file (I was actually trying to consolidate all the RADIUS logs for parsing and storage in a database). In VBScript, here's how it is written

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set folder = objFSO.GetFolder("d:\a")
Set outfile = objFSO.CreateTextFile("d:\testout.txt")

for each file in folder.Files
Set testfile = objFSO.OpenTextFile(file.path, ForReading)
Do While Not testfile.AtEndOfStream
line=testfile.ReadLine
'write to a single output file
outfile.writeline(line)
Loop
testfile.close
Next

outfile.close
Set objFSO = Nothing
Set folder = Nothing
Set outfile = Nothing

Here's how you can do it in PowerShell - with the cmdlet aliases

ls d:\a\ gc ac d:\testout.txt

This should be more than enough reason to learn PowerShell as a system administratior. More to come

Extract user's last password set in Active Directory using PowerShell

Just a follow up on my previous post, here's the script to do just that in PowerShell. It extracts the name and the last time the password was changed and displays it in the host.


$strFilter = "(&(objectCategory=User))"
$Dom = 'LDAP://DC=yourDomain;DC=LOCAL'

$objDomain = New-Object System.DirectoryServices.DirectoryEntry $Dom

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"

$colProplist = "name", "pwdlastset"
foreach ($i in $colPropList)
{$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{
$objItem = $objResult.Properties
$objItem.name
[datetime]::FromFileTimeUTC($objItem.pwdlastset[0])
}

One-liner in PowerShell? Convert a 64-bit (Integer8) value to date time

As I was converting my VBScripts to PowerShell, I reviewed one which checks for the password expiration of a user in Active Directory. The blog post I've had sometime last year extracts object properties and one of them is the pwdLastSet property which specifies a 64-bit value of when the user last changed their passwords. This is necessary if you need to know how many days left before their password expires. One of the things I did was to use a function that converts the 64-bit value to a valid date/time value - thanks to Richard Mueller, of course.

Function Integer8Date(ByVal objDate, ByVal lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
' Account for bug in IADslargeInteger property methods.
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If

If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
End If

lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 - lngAdjust) / 1440

Integer8Date = CDate(lngDate)
End Function

I pass the value from pwdLastSet property to this function to get the date/time value. After a couple of Google searches, I chanced upon a forum post by Brandon Shell - Microsoft PowerShell MVP. The entire function can be summed up in just a line

[datetime]::FromFileTimeUTC($objItem.pwdlastset[0])

This is if you are using the System.DirectoryServices.DirectorySearcher class and not the
System.DirectoryServices.DirectoryEntry in the .NET Framework which is available in PowerShell. The DateTime.FromFileTimeUtc Method converts the specified Windows file time to an equivalent UTC time. So much for using conversion functions in VBScript to do this.

Simple PING test: VBScript vs PowerShell

As a preparation for my TechEd Asia 2008 session on Windows PowerShell, I've been trying to convert a few of my administrative scripts to demonstrate how easy tasks can be done using PowerShell. Here's one I just did: simple PING test. Here's a sample VBScript code that checks whether a PING test is successful or not. I call the VBScript and pass it a parameter which is the hostname or IP address of the computer I want to ping

VBScript:
strComputer=Wscript.Arguments.Item(0) 'parameter passed = hostname

wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)
For Each objStatus In objPing
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
Reachable = False 'if computer is unreacable, return false
Else
Reachable = True 'if computer is reachable, return true
End If
Next

Wscript.Echo Reachable

Here's the equivalent script using PowerShell

Param($hostname)
$ping = new-object System.Net.NetworkInformation.Ping
$Reply = $ping.Send($hostname)
$Reply.status

You can see how much easier it is to accomplish a simple task in PowerShell. It makes the life of an administrator much better, especially if you have to write scripts that automate repetitive tasks. I'll post more samples of the VBScript codes you've probably seen in this blog converted to PowerShell