PowerShell and SqlServer: dropping a set of databases

I have a bunch of databases which follow a prefix pattern.

For resetting my tests, I want to drop them, using PowerShell. Assumption: no concern about anyone/thing accessing those databases at that time.

This does that:

PS C:\PowerShell> Drop-Database -Server .\DWSQL -DatabasePrefix "TestSet" -Verbose

Setdb03

Setdb02

<#
.Synopsis
   Drop set of databases with a given prefix, using a trusted connection.
   Ensure that you do not specify Masters or any system database.
   If no databases are found matching the prefix, it reports and returns.
   Else each found database is dropped, silently.
   If a SQL error happens, then that is reported to the screen.
.Example
   Drop-Database -Server .\DWSQL -DatabasePrefix "TestSet" -Verbose
#>
function Drop-Database
{
    [CmdletBinding(SupportsShouldProcess=$true)]
    Param (
        [Parameter(Mandatory=$true)]
        $Server,
        [Parameter(Mandatory=$true)]
        $DatabasePrefix
    )
    Begin {
    }
    Process {
        $sqlCommand = "set nocount on; select name from sys.databases where name like '$DatabasePrefix%'"
        Write-Verbose $sqlCommand
        $DbNames = @(sqlcmd.exe -S $Server -Q $sqlCommand)
        if ($DbNames.Count -le 2) {
            Write-Host "No database prefix [$DatabasePrefix] found"
            return
        }
        $dropCommand = $DbNames | select -Skip 2 | % { "drop database $_ "}
        $dropCommand | % { Write-Verbose " $_ "}
        $dropCommand | % { & sqlcmd.exe -S $Server -Q "$_"}
    }
    End {
    }
}

cd C:\PowerShell

Advertisements

SqlServer: firewalls, mixed mode authentication

When doing a new install of SQL, I always remember to turn on TCP/IP client access, and to allow SQLServer through the firewalI . But I sometimes forget about the port number, and I did that again today.

From here:

SSFW01

SSFW03

Now it’s fine…

SSFW02


There is a second thing I consistently forget: that Mixed Mode authentication is off by default in a new SQLServer installation:

PowerShell: The basic template in ISE

The basic Ctrl-J template in the PowerShell ISE is a bit noisier than I need. This is all I want:

PSTemplate01

<#
.Synopsis
Drop set of databases
.Example
Drop-Database -DatabasePrefix “TestSet”
#>
function Drop-Database
{
[CmdletBinding(SupportsShouldProcess=$true)]
Param (
[Parameter(Mandatory=$true)]
$DatabasePrefix
)
Begin {
}
Process {
}
End {
}
}

Beyond that, you can use the New-IseSnippet function to make it part of the ISE, and use Ctrl-J to invoke it. Straight from msdn…

$m = @'
Param
(
  [parameter(Mandatory=$true)]
  [String[]]
  $<ParameterName>
)
'@

PS C:\>New-ISESnippet -Text $m -Title Mandatory -Description "Adds a mandatory function parameter." -Author "Kim Akers, Fabrikam Corp." -Force

So applying that principle to our very basic snippet at the top, we get..


$m = @'
<# .Synopsis Drop set of databases .Example Drop-Database -DatabasePrefix "TestSet" #>
function Drop-Database {
[CmdletBinding(SupportsShouldProcess)]
Param (
[Parameter(Mandatory)]
$DatabasePrefix
)
Begin {
}
Process {
}
End {
}
}
'@

New-ISESnippet -Text $m -Title DropDatabases -Description “A snippet to drop databases.” -Author “Dennis Wells, The Things I Do” -Force

Not so fast… So this was OK to execute…

PowerShellSnippetISE01

 

But then…

PowerShellSnippetISE02

Exception calling “Load” with “1” argument(s): “Unable to find or access the file C:\Users\denni\Documents\WindowsPowerShell\Snippets\DropDatabases.snippets.ps1xml”
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ISE\ISE.psm1:102 char:9
+ $psise.CurrentPowerShellTab.Snippets.Load($params[“FilePath”] …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Looking at the file location, the offending file is clearly there…

PowerShellSnippetISE03

, with this content:

PowerShellSnippetISE04

Executing the command line a second time also gives the error.

Hang on…

I recently rebuilt this machine.

If we try this…

New-Item -ItemType File -Path $profile -Force

PowerShellSnippetISE05

And from then on just refer to my old post here

PowerShellSnippetISE07

PowerShellSnippetISE08

PowerShellSnippetISE09

, and as admin..

PowerShellSnippetISE10

Now we shall try again…

PowerShellSnippetISE11

That means I expect to find a snippet with a title of [Mandatory] when I press Ctrl-J in the ISE.

PowerShellSnippetISE12

Hm, so that all works, but it doesn’t seem keen on the carats in the $<parametername>. Fine, don’t care.

I won’t bother with the evidence, but following that little trial, I then ran the DropDatabases snippet through the New-ISESnippet command, and it also appears in the selection list, and can be invoked.

No matter how irritating it is to get these automation-related things working, they SO pay you back for the effort.

PowerShell and Azure: some gotchas

Elsewhere I’ve referenced Keith Mayer. As I needed to remind myself how I did a backup, I started out with “his” suggested first command…

Get-AzureVM

However… that gave me this one:

Get-AzureVM : No current subscription has been designated. Use Select-AzureSubscription -Current <subscriptionName> to
set the current subscription.

Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.GetAzureVMCommand

AzureS201

So try that, although at first I have no idea of my subscription…

AzureS202

Mystery solved, I think: in fact when you go into PowerShell for Azure, the first thing you need to do is not any of the Subscription-sounding functions, but this, i.e. enter

Add-AzureAccount

, that takes you to a credentials dialogue. Once you OK that, it confirms all your details:

AzureS203

But Get-AzureVM still didn’t work… well it didn’t find it, more accurately. So I downloaded it (Azure for PowerShell), and this time it was found. The old blog page gave me the rest of it, more or less.. Here’s where I am now, finishing for the night:

AzureS204 AzureS205 AzureS206 AzureS207

This, in fact is a useful piece:

AzureS208

April 2015

I see that the error message has been made more helpful when your credentials have expired when entering [Get-AzureVM], in that it actually tells you what to do now:

AzureLoginFailed01

Get-AzureVM : Your Azure credentials have not been set up or have expired, please run Add-AzureAccount to set up your Azure credentials.

AzureLoginFailed02

Now run Get-AzureVM again, and they changed the formatting a bit as well:

AzureLoginFailed03

So in summary, after you’ve been away a while, the 2 commands to get to a point where you list your VMs are:

Add-AzureAccount
Get-AzureVM

SSRS 2014: hmm…

This is, as will be plain, just a dump of screenshots, recording my experience with VSU 2013 Update 4, SQLServer Express 2014 including Reporting Services, Data Tools, and generally the whole poorly thought-out show that is SSRS 2014, when you might have thought they would have taken the chance to do a root and branch treatment, get everything into 1 place, rather than a download here, a download there.

ssrs01 ssrs02 ssrs03 ssrs04 ssrs05 ssrs06 ssrs07 ssrs08 ssrs09 ssrs10 ssrs11 ssrs12 ssrs13 ssrs14 ssrs15 ssrs16 ssrs17 ssrs18 ssrs19 ssrs20 ssrs21 ssrs22 ssrs23 ssrs24 ssrs25 ssrs26 ssrs27 ssrs28 ssrs29 ssrs30 ssrs31 ssrs32 ssrs33 ssrs34 ssrs35 ssrs36 ssrs37 ssrs38 ssrs39 ssrs40 ssrs41

 

I’ve been using the SQLServer Express full edition with Data Tools. However when you go to deploy your Reporting Solution in VS2013, note that it doesn’t support Shared Datasets, like it says:

ssrs43

Which is fair enough for free. However, as I need to be deploying reports without having to edit their content, that is no use for me, so I shall now uninstall SQLServer Express, and install the full fat version (I have an MSDN subscription).

AdventureWorks DW 2014

This is fairly useful as a ready-made set of data: I’ve not used the Dim/Fact set before, but may as well, as it goes hand in hand with reporting. Get them here.

 

ssrs44

 

IPad: starting your Mum on her Tech journey

My Mother recently turned 80. Right or wrong, we, the children, bought her an IPad. I knew this could lead to tears: she is of an age never to have touched a PC or a tablet or any form of computer.  I knew all the education would fall naturally to me, and that it would test my considerable powers of patience… and hers, as the bulk of the helpline would be over the phone. This is hard enough on something with a tactile keyboard and a mouse, but trying to guide someone on gesture-based keystrokes over the phone? Argh.

I started out with comic-style guides on paper that she could refer to at home. These were gratefully received and then forgotten. Agreed terms for referring unambiguously to specific buttons and icons were embraced at first (e.g. “dimple”, “hamburger”), but then, when referenced a week later over the phone, it was as if we had never spoken about these words.

She tried “Ipad for Dummies” type books. But have you seen these? For example, this is an extract from one:

ipadseniors01

To someone who has no concept of computers it must look like a random dump of letters.  But as I was writing that, it crossed my mind that I had not yet checked the “intended audience” section of the book. Let’s take a look:

ipadseniors02

So I would say she falls in that category. Conclusion: this book is useless.


I finally did my own video. The tone is a little peremptory, I speak slowly, the content repeats, but to my mind that is an ideal teaching method for someone at this stage. Here’s the video on YouTube.

GmailOnIpad

If you take a look, could you pretty please also let the advert play out… I need those cents 🙂 As you see, it is focussed specifically on Gmail on the IPad, as that is her current interest.

There was then the challenge of her actually knowing how to navigate to YouTube on the IPad. Nope. So I burned a copy to the DVD and sent that. The great thing is… unprompted, she said that it is very useful. Good.

PowerShell: uninstalling MSI-based programs

In the clip below from Programs and Features, we want to uninstall the Acme Mousetrap Thang program:

psUninstall01

$progToUninstall = Get-WmiObject -Class Win32_Product | Where-Object {$_.Name -match "Acme Mousetrap Thang"}

For reasons unclear to me, that can take about 30 seconds to return the object, but anyway, it does it.

Having done that, to uninstall…

$progToUninstall.Uninstall()

psUninstall02

Confirm it’s gone…

psUninstall03

 

If you want to create dummy programs so you don’t impact the stuff you need, see my link here.

If you want to uninstall a set, use -like and “*” as wildcard like this, where I have just selected the first one…

 

Wix02

 

… but then as I did an uninstall using the wildcard (eg…

$progsToUninstall = Get-WmiObject -Class win32_Product | Where-Object {$_.Name -like ‘*SQL Server*’} | Select-Object -First 10

$progsToUninstall | % {$_.Uninstall()}

In fact this is better:

$progsToUninstall | % {$_.Name; $_.Uninstall()}

… so that you can at least spit out the name. Note that mine rebooted a few time, not surprisingly when you’re messing with all the SQL references.

Hm, doubts cast on we32_Product:

http://blogs.technet.com/b/heyscriptingguy/archive/2011/12/14/use-powershell-to-find-and-uninstall-software.aspx