T-SQL & PowerShell – Another way to Identify Database Snapshots

Just a quick blog on spotting your Database Snaphots. I just couldn’t believe that I’ve been missing creating SQL database snapshots but sometimes having so much work make you blind.  I’ve been using a lot Hyper-V Snapshot features and recently (Thanks to Chad Miller) I got the chance to create and test a few.

So, first we need to create a new db snapshot of my AdventureWorks database using T-SQL Script:

CREATE DATABASE AdventureWorks_dbSnapShot_0001 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSQLDENALICTP3\MSSQL\DATA\AdventureWorks_Data_0001.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

Now, I go to SSMS and verify my new database snapshot exist by going into the ‘Object Explorer’ and looking under ‘Database Shashots’ folder.

Using T-SQL

If I use the following T-SQL command to list all my databases:

Select * from [sys].[sysdatabases]

I will get all of them listed including the snapshots. So, here’s another way to use T-SQL to identify all database snapshots. For that, I’m going to do a select and use from the Master db the view named “[sys].[databases]”:

SELECT [name]
      ,[database_id]
      ,[source_database_id]
      ,[owner_sid]
      ,[create_date]
  FROM [master].[sys].[databases]

In this result, pay attention to the “[source_database_id]” column. Notice that this column is mostly “null” except when if it has a value. This value will match the “[Database_ID]” column. So, you can use this to identify database snapshots.

Using PowerShell

Now, let’s take a look on how to use PowerShell to identify database snapshots using SMO. In the following code I’m listing all databases but snapshots are also included.

Import-Module SQLPS -DisableNameChecking
$MySQL = New-Object Microsoft.SqlServer.management.Smo.Server 'YourServername\InstanceName'
$MySQL.Databases | Select name

So, in order to identify the database snapshot I need to look deeper into our .NET database object. Using the “Get-Member” command I can see what’s object are available.

($MySQL.Databases) | Get-Member | Out-Gridview

Using the “Out-Gridview” command to view my results a separate popup window, I found two properties of interest:

  1. IsDatabaseSnapshot‘ – which show “true” us if is a snapshot.
  2. DatabaseSnapshotBaseName‘ – which give us the origne of database name of the snapshot.

So, now I can use the following PowerShell commands to show all my databases and identify the snapshots:

$MySQL.Databases | `
   Select name, Owner, RecoveryModel, `
   IsDatabaseSnapshot, DatabaseSnapshotBaseName `
   FT -AutoSize;

Conclusion

Using both T-SQL and PowerShell examples, now you have a startup point to spot and take some control over your database snapshots.

Happy PowerShelling!

“Windows 8 PowerShell and Hyper-V 3.0 Preview” Slide deck and samples

Last weekend at the “ITPro Camp Saturday” in Sarasota Forida was great event.  Thanks to everyone for participating, and taking the precious time on a Saturday to learn about new and current technologies.  It was a GREAT!!

Here’s my “Windows 8 PowerShell and Hyper-V 3.0 Preview”presentation and demo scripts use during the session:

Please, don’t hesitate to contact me if you have any questions.

Thanks You!

It’s Just Random: PowerShell – Mistery of Redrum Solved!!

It’s Friday and I’m following Jeffery Hicks idea to have fun with PowerShell.  Well, here’s the mistery of Redrum solved one more time.  Some time ago, I remember seen some code for reversing a string of characters.  Well, here’s the one-liner I use to solve the puzzle:

$str = "Redrum"; 
[System.Array]::Reverse(([Array]$RevStr = $str.ToCharArray()));
foreach($chr in $RevStr){ $mistery += $chr }
$mistery;

That’s all for now…. Have fun with PowerShell!

QuickBlog: PowerShell Automating your Credentials

Back in January, I did a quick blog about “Use PowerShell to submit SQLServicePack job to multiple Server“, in that script I have PowerShell to always prompt me for credentials.  But, after a while of typing over and over my password, I found to way to automate my credentials.   Yes!  By automating the credential step, I just could schedule the job, and have time to work with something else.

In this example, I’m passing my credential to the “Start-Transfer” command, so I can do my file download to my destination folder.  Here’s the code snippet to accomplish the automation:

## - Automate to create your credential:
$MyUserName = "Domain\Username";
$MyPassword =  ConvertTo-SecureString 'MyPwd001!' -asplaintext -force;
$MyCredentials = new-object `
-typename System.Management.Automation.PSCredential `
-argumentlist $MyUserName,$MyPassword;

## - Import the module and start the download process of one file:
Import-Module BitsTransfer;
Start-BitsTransfer `
-Credential $MyCredentials `
-Source 'http://YoufilesSite/Files/Demo01.zip' `
-Destination '\\YourServer\NetworkSharedFolder\Demo01.zip';

Ha!  I know what you’re think!  I’m hardcoding my password in the scripts.  So, our possible options would be: 1) you’re the only one running this script (don’t tell your boss),  2) You trust your Network security (humm!), or 3) find the way to encrypt the script so no one can guess the password.

There are products like SAPIEN’s PrimalScript and PrimalForms, that will let you create an executable out of your script, and then you can deploy it to your server.  But, then again, only you can make that decision.  I’m just showing that’s possible in case you need it.

Have fun with PowerShell!

“PowerShell Query for the T-SQL Developer” Slide deck and samples

Once again, Thanks! to Patrick LeBlanc for allowing me to present at today’s (02/15/2012) SQLLunch.com.  It was a Great! turned out with over 60 people attending.  To You All THANKS!!  (Recorded session will be available)

Well, here’s my slide deck presentation, and the sample Demo scripts:

Also, I included the following comparison chart that help in your quest to adopt PowerShell.  This excel file is also included in the zipped file.  This is what I covered today except for “Sum”.

I just realized that this topic could evolved to include a Part II where I could show more on: Group, Sum, Logic, and Functions.  So, later on, I will be posting more information the next “PowerShell Query for the T-SQL Developer” Part II.

Stay Tuned!!

PowerShell Start-Demo now allows multi-lines one-liner

Well, I just couldn’t stop making necessary changes to this great presenter tool.  So, now Start-Demo will process your multi-lines one-liners which this couldn’t be done in the previous version.

So, a good example of a one-liner having line continuation, using the Send-MailMessage cmdlet, where we can use the “`” tick to separate the long command line with its parameters:

## - Send email notification:
Send-MailMessage `
	-To 'Userto@Company1.com' `
	-From 'SenderFrom@Company2.com' `
	-Subject 'Test send from email' `
	-Body 'Check email!' `
	-SmtpServer 'Exchnage.Mailserver.com'

Here’s an example of how it look like using “Start-Demo” in StudioShell:

And, here’s the updated Start-DemoSS.ps1 Version 2.0A 2.0B (02/12/2012):
(apologies for the previous code posted here. It somehow I posted a bad code but I fix it tonight)

###########################################################################
# Original Version: 1.1
# Updated to Version 2.0B, Maximo Trinidad, 02/12/2012
#--------------------------------------------------------------------------
# Comments:
# 1. Customized the foreground color to Cyan and backgroundColor to Black.
# 2. Created a Dump color to default to White.
# 3. Added to put back the default foreground and background colors.
# 4. Commented out the '(!) Suspense' option because Studio Shell can't
#    handle "$host.NestedPrompt".
# 5. Modify the Help menu to acomodate changes.
# 6. Commented out all "$Host.UI.RawUI.WindowTitle".
# 7. Replaced all "[System.Console]::ReadLine()" with "Read-Host".
# 8. Added an end of results 'write-host"-- Press Enter to continue --"'
#    follow with a read-host similate a pause.
#
# Modifications:
# 02/10/2012 - Add section identify oneliners with continuation tick "`".
# 02/10/2012 - Cleanup all unused V1 lines.
# 02/10/2012 - Make code to properly display continuation lines.
# 02/12/2012 - Fix info on Start time and duration.
# 02/12/2012 - Adjust execution message spacing.
#
###########################################################################

function Start-Demo
{
  param($file=".\demo.txt", [int]$command=0)

  ## - Saved previous default Host Colors:
  $defaultForegroundColor = $host.UI.RawUI.ForegroundColor;
  $defaultBackgroundColor = $host.UI.RawUI.BackgroundColor;

  ## - Customizing Host Colors:
  $host.UI.RawUI.ForegroundColor = "Cyan";
  $host.UI.RawUI.BackgroundColor = "Black";
  $CommentColor = "Green"
  $MetaCommandColor = "Cyan"
  $DumpColor = "White"
  $otherColor = "Yellow"
  Clear-Host

  ## - setting demo variables:
  $_Random = New-Object System.Random
  $_lines = @(Get-Content $file)
  $Global:starttime = [DateTime]::now
  $_PretendTyping = $true
  $_InterkeyPause = 100
  $Global:Duration = $null

  Write-Host -for $otherColor @"
Start-Demo: $file - Start time: $starttime
Version 2.0B (02/12/2012)
NOTE: Start-Demo replaces the typing but runs the actual commands.
.

"@
  $continuation = $false;

  # We use a FOR and an INDEX ($_i) instead of a FOREACH because
  # it is possible to start at a different location and/or jump
  # around in the order.
  for ($_i = $Command; $_i -lt $_lines.count; $_i++)
  {
    if ($_lines[$_i].StartsWith("#"))
    {
        Write-Host -NoNewLine $("`n[$_i]PS> ")
        Write-Host -NoNewLine -Foreground $CommentColor $($($_Lines[$_i]) + "  ")
        continue
    }
	else
    {
        # Put the current command in the Window Title along with the demo duration
        $Global:Duration = [DateTime]::Now - $Global:StartTime
        Write-Host -NoNewLine $("`n[$_i]PS> ")
        $_SimulatedLine = $($_Lines[$_i]) + "  "

        for ($_j = 0; $_j -lt $_SimulatedLine.Length; $_j++)
        {
           Write-Host -NoNewLine $_SimulatedLine[$_j]

	           if ($_PretendTyping)
	           {
	               if ([System.Console]::KeyAvailable)
	               {
	                   $_PretendTyping = $False
	               }
	               else
	               {
	                   Start-Sleep -milliseconds $(10 + $_Random.Next($_InterkeyPause))
	               };
	           };

        } # For $_j
		$_PretendTyping = $true

    } # else

   if($_Lines[$_i] -notmatch '`')
   {
		#Write-Host "Yes $($_Lines[$_i])" -BackgroundColor white -ForegroundColor red;
		$_input = Read-Host;
   } #else { $continuation = $true}

    switch ($_input)
    {
################ HELP with DEMO
      "?"
        {
            Write-Host -ForeGroundColor Yellow @"
--------------------------------------------------------------------------------
Start-Demo - Updated to Version 2.0B (12/12/2012)
Help Running Demo: $file
.
(#x) Goto Command #x    (b) Backup     (?) Help
(fx) Find cmds using X  (q) Quit       (s) Skip
(t)  Timecheck          (d) Dump demo  (px) Typing Pause Interval
.
NOTE 1: Any key cancels "Pretend typing" for that line.  Use  unless you
        want to run a one of these meta-commands.
.
NOTE 2: After cmd output, enter  to move to the next line in the demo.
        This avoids the audience getting distracted by the next command
        as you explain what happened with this command.
.
NOTE 3: The line to be run is displayed in the Window Title BEFORE it is typed.
        This lets you know what to explain as it is typing.
.
NOTE 4: Although this script is functional try not to "Goto" a continuation 
        one-liner or it will go to a continues loop. I will correct this sympton
        soon. (02/12/2012)  
---------------------------------------------------------------------------------
"@;
			Write-Host "-- Press Enter to continue --" -BackgroundColor white `
				-ForegroundColor Magenta;
			Read-Host; cls;
            $_i -= 1
        }

      #################### PAUSE
      {$_.StartsWith("p")}
          {
               $_InterkeyPause = [int]$_.substring(1)
               $_i -= 1
          }

      ####################  Backup
      "b" 
		{
	        if($_i -gt 0)
	        {
	            $_i --

	            while (($_i -gt 0) -and ($_lines[$($_i)].StartsWith("#")))
	            {   
					$_i -= 1
	            }
	        }

	        $_i --
	        $_PretendTyping = $false
        }

      ####################  QUIT
      "q"
		{
			Write-Host -ForeGroundColor $OtherColor ""
			$host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
			$host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
			cls;
			return
		}

		####################  SKIP
		"s"
		{
			Write-Host -ForeGroundColor $OtherColor ""
		}

		####################  DUMP the DEMO
		"d"
		{
			for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
			{
				if ($_i -eq $_ni)
				{
					Write-Host -ForeGroundColor Yellow "$("*" * 25) >Interrupted< $("*" * 25)"
				}
				Write-Host -ForeGroundColor $DumpColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
			}
			$_i -= 1
			Write-Host "-- Press Enter to continue --" -BackgroundColor white `
			-ForegroundColor Magenta;
			Read-Host; cls;
		}

		####################  TIMECHECK       
		"t" 
		{              
			$Global:Duration = [DateTime]::Now - $Global:StartTime              
			Write-Host -ForeGroundColor $OtherColor $("Demo has run {0} Minutes and {1} Seconds`nYou are at line {2} of {3} " `
				-f [int]$Global:Duration.TotalMinutes,[int]$Global:Duration.Seconds,$_i,($_lines.Count - 1))
			$_i -= 1
		}

		####################  FIND commands in Demo
		{$_.StartsWith("f")}
		{             
			for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
			{
				if ($_lines[$_ni] -match $_.SubString(1))
				{
				  Write-Host -ForeGroundColor $OtherColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
				}
			}
			$_i -= 1
		};
			
#####################  SUSPEND  # --> not working in StudioShell: help (!)  Suspend (not working)
#
#      {$_.StartsWith("!")}
#          {
#             if ($_.Length -eq 1)
#             {
#                 Write-Host -ForeGroundColor $CommentColor ""
#                 function Prompt {"[Demo Suspended]`nPS>"}
#                 $host.EnterNestedPrompt()
#             }else
#             {
#                 trap [System.Exception] {Write-Error $_;continue;}
#                 Invoke-Expression $(".{" + $_.SubString(1) + "}| out-host")
#             }
#             $_i -= 1
#          }
# --------------------------------------------------------------------------------

      ####################  GO TO
      {$_.StartsWith("#")}
          {
             $_i = [int]($_.SubString(1)) - 1
			 $Scriptline = $null;
			 $continuation = $false;
             continue
          }

      ####################  EXECUTE
      default
          {
             trap [System.Exception] {Write-Error $_;continue;};
			 ## - 02/10/2012-> Commented out original line below
			 # Invoke-Expression $(".{" + $_lines[$_i] + "}| out-host")

			## - add section identify oneliners with continuation tick:
				[string] $Addline = $null;
				if($_lines[$_i] -match '`')
				{
					#Write-Host " Found tick = $($_lines[$_i])" -ForegroundColor yellow;
					$Addline = $_lines[$_i].replace('`','').tostring()
					$Scriptline += $Addline;
					$continuation = $true;
				}
				else
				{
					$Scriptline += $_lines[$_i].ToString();
					$continuation = $false;
				};
				if($continuation -eq $false)
				{
					## - Executive:
					Write-Host " `r`n`t Executing Script...`r`n" -ForegroundColor $otherColor;
					Invoke-Expression $(".{" +$Scriptline + "}| out-host")
				}
			## - --------------------------------------------------------------------
			 if($continuation -eq $false)
			 {
			 	Write-Host "`r`n";
				Write-Host "-- Press Enter to continue --" -ForegroundColor Magenta `
					-BackgroundColor white;
				$Global:Duration = [DateTime]::Now - $Global:StartTime
				Read-Host;
				$Scriptline = $null;
			 };
          }
    } # Switch
  } # for
  ## Next three list to put backl the console default colors and do a clear screen:
  $host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
  $host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
  cls;
  $Global:Duration = [DateTime]::Now - $Global:StartTime; Write-Host "`r`n";
  Write-Host "Start-Demo of $file completed:" -ForegroundColor $otherColor;
  Write-Host -ForeGroundColor Yellow $("Total minutes/sec: {0}.{1}, Date: {2}" `
  	-f [int]$Global:Duration.TotalMinutes, [int]$Global:Duration.Seconds, [DateTime]::now);
} # function

Have Fun with it!

PowerShell Start-Demo makeover for StudioShell

I blogged about a nice Visual Studio addon “StudioShell” that fits very well with SSMSSQL Server Management Studio 2012“, and I found this tool very useful for presenting.  But, when I try running the (long time) famous “Start-Demo” script, it gave me some errors.  Well, Thanks (again) to StudioShell developer Jim Christoper, he gave me a hint to why this was happenning.  Basically, this old “Start-Demo” uses some .NET calls which StudioShell can’t interpret because of the way it was develop, so it need a makeover.

So, I proceed to dive into the code and make some basic changes so it can work inside StudioShell.  Here’s how it looks running it from SSMS executing a demo script, and asking for (?) Help:

Start-Demo running in StudioShell

I change the color scheme, and added a message pause after displaying the result of the onliner.

Showing some of the changes

Also, the new function will work on a normal PowerShell console, and at the end of the start-demo it will put back your default console colors.

Here’s the updated “Start-DemoSS.ps1“code:

###########################################################################
# Original Version: 1.1
# Updated to Version 2.0, Maximo Trinidad, 02/09/2012
#--------------------------------------------------------------------------
# Comments:
# 1. Customized the foreground color to Cyan and backgroundColor to Black.
# 2. Created a Dump color to default to White.
# 3. Added to put back the default foreground and background colors.
# 4. Commented out the '(!) Suspense' option because Studio Shell can't
#    handle "$host.NestedPrompt".
# 5. Modify the Help menu to acomodate changes.
# 6. Commented out all "$Host.UI.RawUI.WindowTitle".
# 7. Replaced all "[System.Console]::ReadLine()" with "Read-Host".
# 8. Added an end of results 'write-host"-- Press Enter to continue --"'
#    follow with a read-host similate a pause.
###########################################################################

function Start-Demo
{
  param($file=".\demo.txt", [int]$command=0)

  ## - Saved previous default Host Colors:
  $defaultForegroundColor = $host.UI.RawUI.ForegroundColor;
  $defaultBackgroundColor = $host.UI.RawUI.BackgroundColor;

  ## - Customizing Host Colors:
  $host.UI.RawUI.ForegroundColor = "Cyan";
  $host.UI.RawUI.BackgroundColor = "Black";
  $CommentColor = "Green"
  $MetaCommandColor = "Cyan"
  $DumpColor = "White"
  Clear-Host

  ## - setting demo variables:
  $_Random = New-Object System.Random
  $_lines = @(Get-Content $file)
  $_starttime = [DateTime]::now
  $_PretendTyping = $true
  $_InterkeyPause = 100
  Write-Host -for $CommentColor @"
NOTE: Start-Demo replaces the typing but runs the actual commands.
.
<Demo [$file] Started.  Type `"?`" for help>
"@

  # We use a FOR and an INDEX ($_i) instead of a FOREACH because
  # it is possible to start at a different location and/or jump
  # around in the order.
  for ($_i = $Command; $_i -lt $_lines.count; $_i++)
  {
    if ($_lines[$_i].StartsWith("#"))
    {
        Write-Host -NoNewLine $("`n[$_i]PS> ")
        Write-Host -NoNewLine -Foreground $CommentColor $($($_Lines[$_i]) + "  ")
        continue
    }else
    {
        # Put the current command in the Window Title along with the demo duration
        $_Duration = [DateTime]::Now - $_StartTime
        #X  - $Host.UI.RawUI.WindowTitle = "[{0}m, {1}s]    {2}" -f [int]$_Duration.TotalMinutes, `
		#       [int]$_Duration.Seconds, $($_Lines[$_i])
        Write-Host -NoNewLine $("`n[$_i]PS> ")
        $_SimulatedLine = $($_Lines[$_i]) + "  "
        for ($_j = 0; $_j -lt $_SimulatedLine.Length; $_j++)
        {
           Write-Host -NoNewLine $_SimulatedLine[$_j]
           if ($_PretendTyping)
           {
               if ([System.Console]::KeyAvailable)
               {
                   $_PretendTyping = $False
               }
               else
               {
                   Start-Sleep -milliseconds $(10 + $_Random.Next($_InterkeyPause))
               }
           }
        } # For $_j
        $_PretendTyping = $true
    } # else

    #X - $_OldColor = $host.UI.RawUI.ForeGroundColor
    $host.UI.RawUI.ForeGroundColor = $MetaCommandColor
    #X - $_input=[System.Console]::ReadLine().TrimStart()
	$_input= Read-Host
    #X - $host.UI.RawUI.ForeGroundColor = $_OldColor

    switch ($_input)
    {
################ HELP with DEMO
      "?"
          {
            Write-Host -ForeGroundColor Yellow @"
--------------------------------------------------------------------------------
Help Running Demo: $file
.
(#x) Goto Command #x    (b) Backup     (?) Help
(fx) Find cmds using X  (q) Quit       (s) Skip
(t)  Timecheck          (d) Dump demo  (px) Typing Pause Interval
.
NOTE 1: Any key cancels "Pretend typing" for that line.  Use <SPACE> unless you
        want to run a one of these meta-commands.
.
NOTE 2: After cmd output, enter <CR> to move to the next line in the demo.
        This avoids the audience getting distracted by the next command
        as you explain what happened with this command.
.
NOTE 3: The line to be run is displayed in the Window Title BEFORE it is typed.
        This lets you know what to explain as it is typing.
---------------------------------------------------------------------------------
"@;
			Write-Host "-- Press Enter to continue --" -BackgroundColor white `
				-ForegroundColor Magenta;
			Read-Host; cls;
            $_i -= 1
          }

      #################### PAUSE
      {$_.StartsWith("p")}
          {
               $_InterkeyPause = [int]$_.substring(1)
               $_i -= 1
          }

      ####################  Backup
      "b" {
                if($_i -gt 0)
                {
                    $_i --

                    while (($_i -gt 0) -and ($_lines[$($_i)].StartsWith("#")))
                    {   $_i -= 1
                    }
                }

                $_i --
                $_PretendTyping = $false
          }

      ####################  QUIT
      "q"
          {
            Write-Host -ForeGroundColor $CommentColor "<Quit demo>"
			  $host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
			  $host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
			  cls;
            return
          }

      ####################  SKIP
      "s"
          {
            Write-Host -ForeGroundColor $CommentColor "<Skipping Cmd>"
          }

      ####################  DUMP the DEMO
      "d"
         {
            for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
            {
               if ($_i -eq $_ni)
               {   Write-Host -ForeGroundColor Yellow "$("*" * 25) >Interrupted< $("*" * 25)"
               }
               Write-Host -ForeGroundColor $DumpColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
            }
            $_i -= 1
			Write-Host "-- Press Enter to continue --" -BackgroundColor white `
				-ForegroundColor Magenta;
			Read-Host; cls;
          }

      ####################  TIMECHECK
      "t"
          {
             $_Duration = [DateTime]::Now - $_StartTime
             Write-Host -ForeGroundColor $CommentColor $(
                "Demo has run {0} Minutes and {1} Seconds`nYou are at line {2} of {3} " -f
                    [int]$_Duration.TotalMinutes,
                    [int]$_Duration.Seconds,
                    $_i,
                    ($_lines.Count - 1)
             )
             $_i -= 1
          }

      ####################  FIND commands in Demo
      {$_.StartsWith("f")}
          {
            for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
            {
               if ($_lines[$_ni] -match $_.SubString(1))
               {
                  Write-Host -ForeGroundColor $CommentColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
               }
            }
            $_i -= 1
          }

#      ####################  SUSPEND
# --> not working in StudioShell: help (!)  Suspend (not working)
#
#      {$_.StartsWith("!")}
#          {
#             if ($_.Length -eq 1)
#             {
#                 Write-Host -ForeGroundColor $CommentColor "<Suspended demo - type 'Exit' to resume>"
#                 function Prompt {"[Demo Suspended]`nPS>"}
#                 $host.EnterNestedPrompt()
#             }else
#             {
#                 trap [System.Exception] {Write-Error $_;continue;}
#                 Invoke-Expression $(".{" + $_.SubString(1) + "}| out-host")
#             }
#             $_i -= 1
#          }

      ####################  GO TO
      {$_.StartsWith("#")}
          {
             $_i = [int]($_.SubString(1)) - 1
             continue
          }

      ####################  EXECUTE
      default
          {
             trap [System.Exception] {Write-Error $_;continue;}
             Invoke-Expression $(".{" + $_lines[$_i] + "}| out-host")
			 Write-Host "-- Press Enter to continue --" -BackgroundColor white -ForegroundColor Magenta;
             $_Duration = [DateTime]::Now - $_StartTime
             #X - $Host.UI.RawUI.WindowTitle = "[{0}m, {1}s]    {2}" -f [int]$_Duration.TotalMinutes, `
			 #      [int]$_Duration.Seconds, $($_Lines[$_i])
             #X - [System.Console]::ReadLine()
			 Read-Host;
          }
    } # Switch
  } # for
  ## Next three list to put backl the console default colors and do a clear screen:
  $host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
  $host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
  cls;
  $_Duration = [DateTime]::Now - $_StartTime
  Write-Host -ForeGroundColor $CommentColor $("<Demo Complete {0} Minutes and {1} Seconds>" `
  	-f [int]$_Duration.TotalMinutes, [int]$_Duration.Seconds)
  Write-Host -ForeGroundColor $CommentColor $([DateTime]::now)
} # function

Please, notice I named the script as “Start-DemoSS.ps1” meaning for StudioShell, still the function will be loaded as “Start-Demo“.

Usage Syntax – To load from script folder location:

.  .\Start-DemoSS.ps1

Usage Syntax – To excute a Demo.txt file:

Start-Demo .\MyDemoScript.txt

What’s inside the demo file? This is a text file containing PowerShell oneliners, and “#” comment lines.

In case you want to try StudioShell: http://studioshell.codeplex.com/

Codeplex StudioShell in SSMS 2012 – Try it!!

If you haven’t yet try it, go ahead download and install this Visual Studio Extension to your SQL Server Management Studio 2012.  This tool will integrate a PowerShell host into your SSMS which you will love for presentations and quick scripts developement from one envirment.  But Yes! this a just a simple host and you’ll find it useful for learning SQL PowerShell.

I will be presenting with this tool in my upcoming webinar with Patrick DeBlanc SQLLunch next week on February 15th “PowerShell Query for the T-SQL Developer” at 11:30am CST / 12:30pm EST.

To download StudioShell, here’s the link: http://studioshell.codeplex.com/

After you install this application, open SSMS,  from the top menu click on “View“, and select “StudioShell“.  Then, you can place this pane anywhere inside your SSMS application.  You can copy/paste t-sql code put remember to import the SQLPS module to have access to your SQL PowerShell commands.

A word of advice! if you venture to go, under ‘Tools | Options“, to change the “Console Choice” to be “Old School“, you will crash your SSMS application when you exit the StudioShell console.  So, DON’T make any changes to your Console Choice options, or you’ll end up loose all your work.

I’m a believer of Tools that can help you be productive, and this one caught my attention.  Please, try it!

Good Job JimChristopher (StudioShell Developer)/@beefarino !!

PowerShell quick list of SQL Users with SysAdmin Role

Here’s a quick way to start getting a list of SQL Server users having “SysAdmin” Role.  Basically, I’m using SQLPS module (now available with SQL Server 2012) which loads all the SMO needed to help you script against your SQL engine.

This script does the following:

  1. Import the SQLPS Module.
  2. Connect to a SQL Server Instance.
  3. Get the SQL Logins information.
  4. Search for SQL users with “SysAdmin” Role, and builds a customized information in a PSObject.
  5. Export the information to a CSV file.
  6. Open the CSV file, which by default could open an Excel application(if installed on machine).

Here’s the code:

Import-Module SQLPS -disablenamechecking

$SQLSvr = "SQLServername\Instancename";
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins = $null;
$SysAdmins = foreach($SQLUser in $SQLLogins)
{
	foreach($role in $SQLUser.ListMembers())
	{
		if($role -match 'sysadmin')
		{
		    Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
		    $SQLUser | Select-Object `
		     @{label = "SQLServer"; Expression = {$SQLSvr}}, `
		     @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
		     Name, LoginType, CreateDate, DateLastModified;
		};
	};
};

$SysAdmins | Export-Csv -Path 'C:\temp\SQLSysAdminList.csv' -Force -NoTypeInformation;
ii 'C:\temp\SQLSysAdminList.csv';

Eventually, you could make changes to this scritp to be capable to access a list of SQL Servers and build your custom report.

Bonus:

To add the functionallity to connect to multiple servers, we can add a list of Servers and then using the “Foreach” statement to loop through the list, and with little changes to the previous code.

Here’s how it will look with just adding a few more line of code:

## - Loads SQL Powerhell SMO and commands:
Import-Module SQLPS -disablenamechecking

## - BUild list of Servers manually (this builds an array list):
$SQLServers = "Server01","Server01\InstanceNameA","Server03";
$SysAdmins = $null;
foreach($SQLSvr in $SQLServers)
{

	## - Add Code block:
	$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
	$SQLLogins = $MySQL.Logins;

	$SysAdmins += foreach($SQLUser in $SQLLogins)
	{
		foreach($role in $SQLUser.ListMembers())
		{
			if($role -match 'sysadmin')
			{
			    Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
			    $SQLUser | Select-Object `
			     @{label = "SQLServer"; Expression = {$SQLSvr}}, `
			     @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
			     Name, LoginType, CreateDate, DateLastModified;
			};
		};
	};
	## - End of Code block

}

## - BUild and open report:
$SysAdmins | Export-Csv -Path 'C:\temp\SQLSysAdminList.csv' -Force -NoTypeInformation;
ii 'C:\temp\SQLSysAdminList.csv';

That’s it!