One of the features of both Visual Studio Code and the PowerShell Integrated Scripting Environment is allowing you to run a line of code with the F8 key.

This is super cool and useful, but it has a side effect because to validly be able to run a specific line of code, you often need to have stored all the results up to this point.

In my experience, I have received all sorts of strange behavior from both PowerShell ISE and Visual Studio code that came down to “oh, I remembered the last time you used that variable and now I am going to cause you problems.”

When I am writing code, I expect that adding a variable would mean that before you add it, it wouldn’t exist, and after you declared it, it would exist, we can see that behavior below:

 

However, in both VS Code and the ISE, if you run the same code again, you might be surprised at seeing the numbers of variables cease to change.

While the benefits are clear and deliberate, I prefer to be able to run from a clean slate each time, and PowerShell gives us a few mechanisms to do that. I thought about Get/Remove variable, but there’s no clear properties that I can see to distinguish user variables from environment ones, so this is what I came up with:

You can see, that now that we defined our code in a scriptblock, the variables do not leak out of their context and we can run our code over and over without worrying about existing variables.

Table Of Contents

• Initial Idea
  • Clarifying Our Requirements
  • The Code
  • Special Thanks

 

Initial Idea

Let”s generate a table of contents for a Github flavored Markdown document in PowerShell (because nobody else seems to have.)

 

Clarifying Our Requirements

After talking with Claudio, he didn”t just want a TOC for a particular markdown file (though that”s nice), but he also wanted a TOC for a set of files in a folder. I decided to do the first one, and it looks like Claudio just finished the second.

I did some googling and found that Github flavored markdown supports a special form of anchor linking as it isn”t supported in vanilla MD. I also found that beyond this there wasn’t a good anchor link MD shortcut, so I took this tact (which cares more about readability than speed or length.)

You can use the function below, which was also run on this blog post to generate itself.

 

The Code

function Convert-StringToMarkdownTableOfContents {
    param ([string]$MarkdownDocument)

    $nl = [System.Environment]::NewLine
    $TOC = "## Table Of Contents$nl$nl"

    foreach ($line in $MarkdownDocument.Split( $nl )){
        if ($line -match "^(#+)\s(.*)$") {
            $Count = $Matches[1].Length
            $Content = $($Matches[2])

            $EncodedContent = ($Content -replace " ",''-'').ToLower()

            if ($Count -eq 1){
                $TOC += "- [$Content](#$EncodedContent)$nl"
            }
            else {
                $TOC += "$("  " * $Count)* [$Content](#$EncodedContent)$nl"
            }

            $MarkdownDocument = $MarkdownDocument -replace "(($line)\w*)",$(
                ''<a href = "#'+ $EncodedContent +''"></a>'' +
                $nl +
                $line
            )
        }
    }
    return $TOC + $nl + $MarkdownDocument
}

You can download this code which generates this post Here.

 

Special Thanks

Special thanks to Cláudio Silva (PowerShell MVP) for giving me this fun idea!

Note:
Unlike Github Flavored Markdown, WordPress Markdown does not implement the anchor links, so in my blog they wont be clickable like they would github, sorry!

As a part of the T-SQL Tuesday run by Rob Sewell this month, I wanted to throw in a little post about using Test-DbaLastBackup which I will also be showing off a this year’s SQL Summit!

 

Come to the Precon

If you like this post, don’t forget to signup for me and Chrissy Lemaire’s precon at the 2017 SQL Summit for more awesome PowerShell <3 SQL Server magic with dbatools!

Test-DbaLastBackup

As data professionals we know that safeguarding our data is one of our most important jobs, and yet a significant amount of us continue to find excuses not to verify the integrity of our data.

I want to show you an uber simple method to automate testing your entire estate worth of backups.

Before we move forward, I want to note this process does perform a restore and DBCC checkdb on the server you specify, so please don’t run this in production and come to me upset that your IO and CPU time shot through the roof 😀

However, if you only have a smallish or sleepy production environment, this will run just fine side by side, otherwise I demonstrate a parameter to run this on a different server.

Lastly, before we begin, this assumes you’ve already installed and imported dbatools, because why wouldn’t you have?

Code

"sql2012","sql2005" | Test-DbaLastBackup -Destination "Sql2016"

This:

• Defines a list of two servers (PowerShell lists are as easy as “”,””)
• Pipes them to the Test-DbaLastBackup command.
• Which then:
  • Gathers information about the last full backups for all of your databases on that instance.
  • Restores the backups to the Destination with a new name. If no Destination is specified, the originating server will be used.
  • The database is restored as “dbatools-testrestore-$databaseName” by default, which you can change with the -Prefix parameter.
  • The internal file names are also renamed to prevent conflicts with original database.
  • A DBCC CHECKTABLE is then performed.
  • And the test database is finally dropped.

If you are super stuck on the restore portion but would like to do a VERIFYONLY, there is a -VerifyOnly parameter switch.

Some other useful parameters you might need to use in a typical scenario:

-Database Allows you to specify a list of databases to specifically check, useful if you have just one in mind.

-MaxMB Prevents databases over X MB from being restored.

-ExcludeDatabase Exclude specific databases (for that one gigantic one you know you don’t want to touch.)

-DataDirectory Alternate data directory (besides default) you want to restore the database files to test.

-LogDirectory Alternate log directory (besides default) you want to restore the database files to test.

Watch

If you like video, wait no more, here’s a youtube demo of Test-DbaLastBackup

https://www.youtube.com/watch?v=50xEuEZr6as

Thanks for your time, and I hope you enjoyed.