Month: February 2012

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:

[sourcecode language=”SQL”]
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
[/sourcecode]

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:

[sourcecode language=”SQL”]
Select * from [sys].[sysdatabases]
[/sourcecode]

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]”:

[sourcecode language=”SQL”]
SELECT [name]
,[database_id]
,[source_database_id]
,[owner_sid]
,[create_date]
FROM [master].[sys].[databases]
[/sourcecode]

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.

[sourcecode language=”PowerShell”]
Import-Module SQLPS -DisableNameChecking
$MySQL = New-Object Microsoft.SqlServer.management.Smo.Server ‘YourServername\InstanceName’
$MySQL.Databases | Select name
[/sourcecode]

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.

[sourcecode language=”PowerShell”]
($MySQL.Databases) | Get-Member | Out-Gridview
[/sourcecode]

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:

[sourcecode language=”PowerShell”]
$MySQL.Databases | `
Select name, Owner, RecoveryModel, `
IsDatabaseSnapshot, DatabaseSnapshotBaseName `
FT -AutoSize;
[/sourcecode]

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!

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

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:

[sourcecode language=”powershell”]
## – 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’
[/sourcecode]

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)

[sourcecode language=”powershell”]
###########################################################################
# 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
[/sourcecode]

Have Fun with it!

I blogged about a nice Visual Studio addon “StudioShell” that fits very well with SSMS “SQL 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:

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

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:

[sourcecode language=”powershell”]
###########################################################################
# 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
[/sourcecode]

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/

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