Thank you Shauna Theobald and the Novell OSTC for providing the venue! The fourth Wednesday in December seems too close to Christmas, so I think we’ll meet again on January 27.
Don Jones has a great video presentation entitled, “Getting to Know PowerShell” that covers much of this morning’s material as well.
Buy your breakfast at the East Bay Cafe, have a seat, and learn about PowerShell for one hour. That’s the basic premise of the monthly PowerShell Power Breakfast. PowerShell provides for Windows what shells like Bash provide in Linux – a command line for one-off tasks or automation, be they simple or complex. Schedule this event on your calendar, and bring your manager and/or coworkers.
I’ll be presenting a rapid-fire introduction to PowerShell, and will be followed by Ben Miller, current president of the Salt Lake City SQL Server Users Group. Ben will present on Gathering SQL Server Performance Data With PowerShell. Both presentations will be brief, and if time allows, we’ll hold a free-form discussion afterward.
The following screen shot shows a directory listing(yes, that ls alias is installed by default), retrieval of BIOS information, and password generation using a .NET method, all using PowerShell.
Windows PowerShell Quick Reference
Dig Out by Digging Into PowerShell
The PowerShell Power Breakfast is open to any individual interested in Windows PowerShell, from veteran developers and sysadmins to those just starting with automation & scripting in a Windows context.
Where: Novell Cafeteria, Building G, Provo Campus (map)
Directions: Take the University Ave exit off I-15, cross University Ave, and turn left (north) onto Novell Place and enter the Novell campus. When you drive up to bldg H (the 8-story bldg), turn left and park in the SW parking lot. The sidewalk on the west side of bldg H will take you to the cafe (bldg G). We'll be in the conference room at the far north end (past the food court).
Ever wanted to know which service accounts SQL Server is using?
1. Make a text file containing all the machine names of your SQL Servers – don’t worry about multiple instance names or virtual names, just put the machine names, one per line. This file will be used to populate the service account info once for each server listed.
2. Create a text file with the following, and save it as get-sql-svcaccounts-wmi.ps1 to be run in PowerShell.
$erroractionpreference = "SilentlyContinue"
foreach ($server in get-content("2009-10-27-sql-servers.txt"))
{
$class = Get-WmiObject -computername $server -namespace root\Microsoft\SqlServer\ComputerManagement -class SQLService
"`n" + $server + ":"
foreach ($instance in $class) {$instance.HostName + ", " + $instance.ServiceName + ", " + $instance.StartName}
}
Why silently continue in case of error? Well that’s for those lovely Windows 2000 boxes that are _still_ on your network.
Here’s a screenshot:
Here’s a PSSERVICE.EXE + PowerShell script that will work with Windows 2000 boxes. The script builds a PSSERVICE string & runs the string using Invoke-Expression and passing the prefix of each service name.
1. Make a text file containing all the machine names of your SQL Servers.
2. Create a text file with the following, and save it as get-sql-svcaccounts.ps1 to be run in PowerShell.
foreach ($server in get-content("2009-10-27-sql-servers.txt"))
{
$exp1="psservice \\"
$exp2=" security MSSQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
#debug
#echo $cmd_line
"`n" + $server + ":"
Invoke-Expression $cmd_line
#part 2
$exp2=" security MSDts | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
Invoke-Expression $cmd_line
#part 3
$exp2=" security MSFTE | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
Invoke-Expression $cmd_line
#part 4
$exp2=" security SQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
Invoke-Expression $cmd_line
#part 5
$exp2=" security Report | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
Invoke-Expression $cmd_line
#part 6
$exp2=" security MSOLAP | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
$cmd_line = $exp1 + $server + $exp2
Invoke-Expression $cmd_line
}
Yes, I realize the above could be much shorter – if you rewrite it I’ll edit this post and give you credit.
Here’s a screenshot of the PSSERVICE approach:
That’s it!
References:
http://xinn.org/wmic.html
http://www.eggheadcafe.com/software/aspnet/30426636/change-service-account-pa.aspx
http://www.mattwilsoninc.com/microsoft/the-many-ways-to-control-windows-services/
http://www.myitforum.com/articles/40/view.asp?id=12441
Tested on SQL Server 2005 & SQL Server 2000
I wrote up a post on sp_helprotect2 previously, but I like this method better, even though they both use stored procedure sp_helprotect to populate the table.
--T-SQL to generate all rights for the current database in valid T-SQL statements
create table dbo.##protect (
[owner] sysname,
[object] sysname,
[grantee] sysname,
[grantor] sysname,
[protectype] sysname,
[action] sysname,
[column] sysname
)
go
insert into dbo.##Protectexec sp_helprotect
go
select
protectype + action + ' ON ' + object + ' TO [' + grantee + ']'
from dbo.##protect
where object <> '.'
order by action
go
drop
table dbo.##protect
go
Maybe you’re a SQL Server DBA and you prefer 64K cluster sizes for optimizing disk I/O to read an extent at a time, or you just need to know how a volume was formatted. The command line can help using fsutil:
fsutil fsinfo ntfsinfo c: Sample output:
“Bytes per cluster” shows the cluster size in use on the volume letter specified – in this case, 65536 bytes, or 64KB.
Cue Dora the Explorer “We Did It” music.
References:
http://www.tech-archive.net/Archive/WinXP/microsoft.public.windowsxp.basics/2008-06/msg00172.html
http://www.sqlservercentral.com/Forums/Topic242971-65-1.aspx#bm243468
Last Wednesday night I went to the Miller Free Enterprise Center on the Sandy Campus of Salt Lake City Community College to help set up for UTOSC 2009 – this year’s Utah Open Source Conference. My first marching orders from a seasoned network admin were to cut and crimp some cables for the exhibitor booths/tables. Turns out there is a best way to tape cords and cables to the floor, and it involves a specific kind of tape that techs who accompany concert tours and assemble movie sets use: it’s called gaffer’s tape. Gaffer’s tape tears like cloth tape, but is much stronger, and unlike duct tape, leaves no adhesive residue behind when removed. After false starts at Home Depot & Graybar, I purchased a 60yd roll for $20(after taxes) at the local Guitar Center. Usually it comes in matte black as to not reflect any light, but the roll I purchased was white for high visibility(cords are a trip hazard, even when taped down). Cheap imitations of gaffer’s tape exitst, but if you paid less than $15 a roll it was likely not the kind of tape a gaffer on a film crew would use. Graybar sold me something similar called Electrician’s Bundling Tape, but it didn’t tear nearly as well, and cost around $7 per roll.
Mounting tape was another adhesive used in conference setup. I needed to tape an additional Cisco wireless access point(conference attendees were saturating the wireless spectrum with their array of RF devices – I think we added 4 APs to the venue) to a glass window, and this mounting tape worked so well I wonder how somebody managed to remove the equipment afterward. A $500 radio within arm’s reach might be in danger of being stolen, and any safeguard preventing a casual thief from walking away with this hardware seemed a judicious choice.
Next year I want to be able to attend more presentations, so in the future think I’ll limit my volunteer efforts to the night before the event. Presenters were very technically oriented, and the raw knowledge disseminated more than made up for an occasional lack of salesman-like polish in delivery. As a geek I appreciate more substance and passion than hollow style. When a fellow geek noticed I was running Windows 7 on my laptop and quipped regarding the same, a guy I didn’t know across the table from me shot back, “Open Source is more than Linux”.
UTOSC speakers had a focus on automation and uptime – the theme of the conference was (afford | scal | reli)ability. Another attendee remarked at dinner that in his experience, *nix admins tended to focus more on automation of processes than their Windows counterparts. Or as another presenter, Christer Edwards, put it, “A good admin is a lazy admin.” I agree. Next year I’m going to offer to present on automating processes within - you guessed it – Windows.
I thought my precious 2GB Sansa Clip MP3 player was destroyed when I found it had gone through our Fisher & Paykel washing machine. I let it dry out for a few days in a bag of uncooked rice.
When I finally powered on the Sansa Clip the screen lit up, but nothing legible was displayed. It was as if all the pixels were turned on but were very dim. Then the device powered off. I thought the display was shot.
2 months later my son convinced me to recharge the device. I was able to read files from the device, but when I turned it on, the display problem persisted. Only this time, when all the pixels lit up they were noticeably brighter. I looked in the Sansa manual to find ways blindly navigate the controls, and found an option to reset the player by holding the switch in the powered off position for 15 seconds, so I tried that instead.
When I again turned on the Clip, the display worked! The device had survived with no apparent ill effects, other than needing a reset. I had to take a picture. 
This thing is small and tough, and unlike the Apple ipod firmware, it plays Ogg Vorbis(some of my preferred podcasts are in that format). Thank you SanDisk engineers!
link –s
A command that hearkens to the goodness of Linux…
Windows can do that, too!
My wife recently created a directory for scanned documents on her system under the "My Documents" folder.
I wanted this new folder that my sweetie named, "My Paperless Lifestyle", to automatically upload/sync to her Dropbox account. This would allow me to access the files on any computer with Dropbox installed.
I looked at the preferences for Dropbox on her machine, and Dropbox only syncs files that are stored under the
"c:\Documents and Settings\debbie\My Documents\My Dropbox"
folder in my wife’s profile.
Debbie’s machine is running Windows XP, so I grabbed the JUNCTION.EXE utility from Microsoft (thank you, Mark Russinovich!) to make a "fake" folder (junction, or symbolic link) under Dropbox that would point to the scans. A machine running Vista or higher would use the command MKLINK instead. Now the scans can be accessed on any computer with Dropbox installed. I can then access the stuff Deb scans on my work computer or laptop, and the added bonus is I don’t have to mess up my wife’s existing folder/organizational structure.
Here’s the command:
C:\> junction -s "c:\Documents and Settings\debbie\My Documents\My Dropbox\scans" "C:\Documents and Settings\debbie\My Documents\My Paperless Lifestyle" 
Now let’s open the Dropbox folder, and…
There it is, an NTFS junction point called scans, that actually refers to the
"C:\Documents and Settings\debbie\My Documents\My Paperless Lifestyle" folder.
If a directory listing is output it looks like this:
<JUNCTION> doesn’t really tell us the secret location of where scans really points, so instead of using DIR, here’s the JUNCTION command with scans as a parameter.
That’s it! Now you can fool programs into thinking files are in one place, when the files are actually stored in your preferred location.
I’ve used this same technique to have multiple users on the same machine use the same Firefox profile. This helps prevent me from installing the same Add-ons for Firefox for each one of my children individually. I like reducing redundancy and the machine is configured so that only one user can be logged on at a time(it’s a netbook).
Maximum PC has a great article with examples on using MKLINK in Vista, Windows 7, etc. Here is another post explaining the differences between symbolic links and hard links(junctions are the directory version of hard links) in a Windows context.
Thanks Chris, for telling me about PathPing!
PathPing is a network utility supplied in Windows NT, Windows 2000, Windows 2003, Windows XP and Windows Vista. It combines the functionality of Ping with that of Traceroute (in Windows: tracert), by providing details of the path between two hosts and Ping-like statistics for each node in the path based on samples taken over a time period, depending on how many nodes are between the start and end host. The advantages of PathPing over ping and traceroute are that each node is pinged as the result of a single command, and that the behavior of nodes is studied over an extended time period, rather than the Ping's default sample of four messages or Traceroute's default single route trace. The disadvantage is that, using the default settings, it often takes more than five minutes to produce a result.
PathPing - Wikipedia, the free encyclopedia
Use the -q number option to reduce the number of queries per hop if you're in a hurry, as the default is 100.
From: How to Get Information About Installed Applications Without Using WMI by Alex K. Angelopoulos
In PowerShell, the simplest way to display the [Installed Applications] is to use the Get-ChildItem cmdlet (which has the alias of gci), then pipe its results to the Get-Item- Property cmdlet. (Get-ChildItem doesn’t retrieve information about the registry values contained within subkeys; it only lists the subkeys’ names.) So, the command that you’d enter in the PowerShell window would be
gci “HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall” |ForEach-Object{Get-ItemProperty $_.PSPath}
I changed the syntax a bit, and here’s what I prefer:
dir HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall | ForEach { Get-ItemProperty $_.PSPath} | Select DisplayName,InstallDate, DisplayVersion | Sort DisplayVersion, Installdate | Format-Table * -auto The ability to change the sort order is handy for software inventory(When was that app installed?) and version troubleshooting, and
| Format-Table * –auto
I just installed Windows 7 Ultimate Build 7100(32-bit) on my old Thinkpad T60p(purchased in Oct. 2006) and am creating this post with it.
Took 37min 34secs (including 5 reboots) to install the OS and all the ‘important’(and a few optional) updates. The initial install without updates took only 28 minutes.
When right-clicking on the desktop the following appeared:
Changing the Screen resolution is available from the desktop context menu! How cool is that? Pressing the Windows key + P also gives some simple projector display options.
I’m guessing the install required less than 10 mouse clicks – impressive. Here’s hoping final code gets released in October of this year.
“Hey, can you get me a copy of the production database on the dev server?”
Aside from the fact that this request can be a really bad idea if sensitive data in a database is available on an unsecured dev server, there are scenarios where this is just fine - _IF_ the backup chain is not broken. Enter the COPY_ONLY flag available in SQL Server 2005. The sp_BackupNow stored proc uses the COPY_ONLY flag, and was pieced together from bits and pieces I found around the web, such as the timestamp used in the backup filename. Customizations followed to suit my needs. I added a self-documenting feature so that when run without parameters, some brief usage and examples are displayed. In addition, you’ll need a server share on the target server that allows the source server’s SQL Server Service user account write rights, since the proc will save the backup to a network location.
Go ahead and poke holes, or add this to your DBA toolbox.
The docs for sp_BackupNow follow, and the sp_BackupNow T-SQL is here.
Backing up production databases for restoring to development/test environments
Requirements/Context:
SQL Server 2005 (source and destination servers)
Writable Share named BackupWrite$ on Target Development Servers
sp_BackupNow stored proc on source server
Appropriate Database and Share Permissions
Description:
Source server creates a backup using the COPY_ONLY flag that is written to the target development server on the BackupWrite$ share. By using the COPY_ONLY flag(available in SQL Server 2005 and newer) the production backup chain is unaffected by the Ad hoc backup operation.
When executing the stored procedure sp_BackupNow, provide parameters for the following:
@dbname the database being backed up
@DestinationServer the target server where the backup will be saved to share BackupWrite$
@backuptype 'USERBACKUP' or 'ARCHIVE'. Backups of type ARCHIVE are saved to a separate folder to facilitate long-term retention in TSM or other backup software if desired.
@UserBackDir The first name of the user requesting the backup is used to save the file to a folder of the same name under the share BackupWrite$ – this folder must already be created.
USERBACKUP Usage Example:
EXEC sp_BackupNow @dbname='MyDatabase', @DestinationServer='DEVSERVER', @backuptype='USERBACKUP', @UserBackDir='Fred'
The above example will save a backup of database MyDatabase to directory \\DEVSERVER\BackupWrite$\Fred\
The filename of the backup contains the databasename with a timestamp appended, such as
\\DEVSERVER\BackupWrite$\Fred\MyDatabase__20090130154713.BAK
The output displayed by the above example will be similar the following:
26 percent processed.
52 percent processed.
78 percent processed.
85 percent processed.
90 percent processed.
Processed 488 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
100 percent processed.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 489 pages in 0.439 seconds (9.108 MB/sec).
@dbname=MyDatabase
@DestinationServer=DEVSERVER
@backuptype=USERBACKUP
@filename=\\DEVSERVER\BackupWrite$\Fred\MyDatabase__20090130154713.BAK
@backupname=MyDatabase AdHoc Backup
ARCHIVE Usage Example:
EXEC sp_BackupNow @dbname='MyDatabase', @DestinationServer='DEVSERVER', @backuptype='ARCHIVE'
The above example will save a backup of database MyDatabase to directory \\DEVSERVER\BackupWrite$\Archive\
The filename of the backup contains the databasename with a timestamp appended, such as
\\DEVSERVER\BackupWrite$\Archive\MyDatabase__20090130155825.BAK
The output displayed by the above example will be similar the following:
26 percent processed.
52 percent processed.
78 percent processed.
85 percent processed.
90 percent processed.
Processed 488 pages for database 'MyDatabase', file 'MyDatabase' on file 1.
100 percent processed.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 489 pages in 0.360 seconds (11.107 MB/sec).
@dbname=MyDatabase
@DestinationServer=DEVSERVER
@backuptype=ARCHIVE
@filename=\\DEVSERVER\BackupWrite$\Archive\MyDatabase__20090130155825.BAK
@backupname=MyDatabase AdHoc Backup
I’ve played with a few tools to post code to my blog and for sharing documentation. I’m mostly interested in pasting T-SQL from SQL Server Management Studio(pasting RTF), but I also paste PowerShell scripts and batch files.
Now I’m trying the Insert Formatted Clipboard plugin from Noah Coad along with Windows Live Writer – both new to me with this post. There was a painful install process(see comments by ITSOLVE) under Vista that involved renaming an .MSI file, executing msiexec from the command prompt, and manually copying a DLL file. In XP I had no problems with the install.
On to usage. I copied the T-SQL from SSMS
to the clipboard, and then, in Windows Live Writer clicked
Insert >> Formatted Clipboard… 
Here’s what pasted:
Ahh. Cleanly converted HTML from an RTF paste – with inline styles. Hallelujah! You may notice that the pasted text does not contain the proper monospaced typeface. I’m ok with that, as I would normally wrap that text in a border with a monospaced font. Read on.
Sometimes I just want to wrap the code with a border following the styles created at Format My Source Code for Blogging.
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code>
</code></pre>
SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()
If you’d like to dive even further into RTF, HTML, and related macros for Visual Studio, see Steve Nyholm’s blog.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | out-null
$s = New-Object ("Microsoft.SqlServer.Management.SMO.Server") 'MYSQLSERVER'
#Current Transaction Log Size in MB
$s.databases["MyDB"].Logfiles[0].Size/1KB
$s.databases["MyDB"].LogFiles[0].Shrink(2, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::Default)
$s.databases["MyDB"].Logfiles.refresh($true)
$s.databases["MyDB"].Logfiles[0].Size/1KB
Automation Using SQLCMD with batch
Why SQLCMD?
Repetitive processes, same targets, deployment tasks
Scenarios
Run schema change on multiple server/db targets
CREATE/ALTER stored proc on multiple server/db targets
Query Permissions on multiple server/db targets
Speed! Can be much faster than GUI
SQLCMD basics
install location:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE
-S SERVERNAME
-d DBname
-i inputfile
>> append operator
CMD Batch Overview (2K, XP, 2003, Vista, Server 2008)
REM I love comments!
:: Protect yourself at the command line with this!
%1 first argument passed
%2 second argument passed
Arguments examples – copying files to a server
copy-backups.cmd
robocopy /MAXAGE:1 "%1" "%2" "*.bak"
copy-winrar.cmd
robocopy /S "%programfiles%\WinRAR" "\\%1\c$\Program Files\WinRAR" *.exe
use self-documenting batch files - commented examples or output when no arguments are passed
IF "%~1"=="" goto help
Putting it all together
Examples:
Demonstrate sqlcmd to multiple targets, then parse command line by copying everything before the append operator.
The batch file, ONEOFF.CMD contains the targets for the T-SQL to be executed.
ONEOFF.CMD accepts two arguments: the name of the .SQL file containing the T-SQL commands, and a log file for output.
I saved all 3 files to the same directory( C:\BIN ) to simplify the example and ran the following:
C:\BIN>ONEOFF.CMD ONEOFF.SQL ONEOFF.LOG
------------------- -------------------- -----------------------
-- T60-LAPTOP Chile 2009-01-29 18:43:22.793
------------------- -------------------- -----------------------
-- T60-LAPTOP Cuba 2009-01-29 18:50:13.543
Msg 156, Level 15, State 1, Server T60-LAPTOP, Procedure sp_BackupNow, Line 7
Incorrect syntax near the keyword 'BEGIN'.