Wednesday, November 25, 2009

Files from PowerShell Power Breakfast Kickoff Meeting


Here is zip file containing the scripts, notes, slides, and some online books used for today’s introduction to PowerShell.

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.

Monday, November 09, 2009

PowerShell Power Breakfast in Utah – Kickoff Meeting 8:00am-9:00am, Wednesday, November 25, 2009

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.

Schwag will be given to a few lucky attendees, including this OGIO backpack,38 038 - cropped,resampled courtesy of Redmond and local Microsoft Technology Strategist Dan See.  Thanks, Dan!

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.PowerShell_Breakfast01

Did that pique your interest?  Come to the breakfast to learn more!

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).

Tuesday, October 27, 2009

List all SQL Server-Related Service Accounts using WMI under PowerShell

Ever wanted to know which service accounts SQL Server is using?

Fast PowerShell WMI Method(doesn’t work with boxes running Windows 2000)

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:


Slow PSSERVICE + PowerShell Method(works with boxes running Windows 2000 and later OS versions of Windows)

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

#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!


Thursday, October 22, 2009

T-SQL to generate all rights for the current database as valid T-SQL statements

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

into dbo.##Protect

exec sp_helprotect

select protectype + action + ' ON ' + object + ' TO [' + grantee + ']'

from dbo.##protect
where object <>
order by action

drop table dbo.##protect

What’s my NTFS Cluster Size?

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.


Monday, October 12, 2009

What’s gaffer’s tape? Volunteering at the 2009 Utah Open Source Conference


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.

Monday, October 05, 2009

SanDisk Sansa Clip Survives Washing Machine 1000 RPM Journey in Pant Pocket

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.
Sansa Clip Resurrection 004-cropped

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!

Monday, September 28, 2009

Links and Junctions in Windows (yes, even in XP)

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.

Wednesday, August 26, 2009

PathPing > Traceroute

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.PathPing

Monday, August 24, 2009

PowerShell: Get Information About Installed Applications Without Using WMI

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

”AutoFits” column widths.


Thursday, May 14, 2009

Right-Click on Desktop >> Screen Resolution (Windows 7 Top UI feature)

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.

Tuesday, April 07, 2009

Ad Hoc Backups Without Breaking Backup Chain in SQL Server 2005

“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


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

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$
'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


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).

@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


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).


@backupname=MyDatabase AdHoc Backup


Wednesday, April 01, 2009

Pasting RTF to HTML in blogs using Insert Formatted Clipboard plugin for Windows Live Writer


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:

SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()

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> 


So, using the above html I can paste inside the <code> </code> tag and it comes out like so:

SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate() 

Nice, eh?

Additional notes:

If I’m careful within Windows Live Writer I can highlight & copy the text that was created from the Insert >> Formatted Clipboard… step, then double-click on the newly created empty border and paste right into the border style without using the Source Tab.

The other Windows Live Writer plugin used above to display the <pre> style from Format My Source Code for Blogging was Code Snippet by Leo Vildosola.

If you’d like to dive even further into RTF, HTML, and related macros for Visual Studio, see Steve Nyholm’s blog.

Monday, March 16, 2009

Shrinking Transaction Logs in PowerShell

[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].Shrink(2, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::Default)


Friday, February 20, 2009

Automation Using SQLCMD with Batch

In the January 2009 Meeting of the Utah County SQL Server Users Group I gave a presentation on using SQLCMD.EXE with batch files for automating T-SQL scripts.

Automation Using SQLCMD with batch

  1. Why SQLCMD?

    1. Repetitive processes, same targets, deployment tasks

      1. Scenarios

        1. Run schema change on multiple server/db targets

        2. CREATE/ALTER stored proc on multiple server/db targets

        3. Query Permissions on multiple server/db targets

        4. Speed! Can be much faster than GUI

  2. SQLCMD basics

    install location:
    C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

    -d DBname
    -i inputfile
    >> append operator

  3. 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

    1. Arguments examples – copying files to a server

      robocopy /MAXAGE:1 "%1" "%2" "*.bak"
      robocopy /S "%programfiles%\WinRAR" "\\%1\c$\Program Files\WinRAR" *.exe

    2. use self-documenting batch files - commented examples or output when no arguments are passed

      IF "%~1"=="" goto help

  4. Putting it all together


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:


The log file can be very helpful in determining which servers/databases successfully parsed the commands, and which targets failed. The first part of the .SQL file contains the following 5 lines:

'-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()

These 5 lines above format the outputted log file with the server name, database name, and timestamp. In the example log file output below, the "server" name is my laptop(T60-LAPTOP) since I ran the demo locally. The first 3 lines show that database Chile accepted the .SQL without errors. Lines 4-7 show errors when running against database Cuba.
------------------- -------------------- -----------------------
-- 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'.


SQLCMD standalone installer(Microsoft SQL Server Native Client required for install, found on same page)

Using the sqlcmd Utility

Command line reference

NotePad ++