Archive for Windows PowerShell
Indulge in free online SQL Server training on March 21, 2012 beginning at 00:00 GMT (that’s a March 20 evening start for …
Categories : Edwin Sarmiento
Back in 2009, I wrote an article about how to generate SQL scripts using Windows PowerShell and Server Management Objects …
Categories : Edwin Sarmiento
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
Categories : Edwin Sarmiento
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
Here's an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication
Categories : Edwin Sarmiento
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
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
Categories : Edwin Sarmiento, Hyper-V
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
}
# 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
}
Categories : Edwin Sarmiento
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
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
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
Categories : Edwin Sarmiento
Search
Support
Third Tier provides advanced support services to IT Professionals. Learn about what we do at http://www.thirdtier.net or click on the support icon below to chat with one of our support representatives.
Third Tier
Copyright © 2012 All Rights Reserved
Copyright © 2012 All Rights Reserved