PowerShell – using SQLPS for backup and restore

For this, I set up a Windows 10 Enterprise VM, and loaded up Localdb.msi and the new 2017 SSMS.

Code is here.

Useful MSDN page here.

Some screenshots


Azure ARM: naïve single VM deployment

The aim is to get a real simple VM deployment working using a json template, and in doing that to sacrifice any guidelines on authoring best practice, so we can focus on getting familiar with the nuts and bolts of ARM deployment.

Download the template and PowerShell helper from here.

Assume you need Windows 10 Anniversary edition PowerShell versions for these client/deployment actions.

Open a PowerShell admin prompt in the download location, and run [.\deploy.ps1].

You will be prompted for your Azure login, a resource group and location (e.g. “uksouth”), and an admin password for the VM.

If all goes fine, then you will be able to rdp to the VM, and be able to start up SSMS.

Finally, a couple of pictures I took to focus the mind when trying to reduce the template to the bare minimum to get a working VM:

The next day… in fact I have started to remove the literals as I get more familiar. Again, I refer you the MS Azure docs above.

Azure ARM: listing the images available to your subscription

I need to identify the images that are available to my subscription. For example, if I want an image that gives me SQL Server 2016, short of Googling, how do I find that?

I seem to remember that this was kind of easy in the Classic Azure deployment model, but I am now using Azure Resource Manager (ARM), and I don’t wish to return to Classic, i.e. legacy, which is not where the development effort is being invested.

For my use-case, I need to inject the answer (identifying the available images) into a ARM deployment template, but I think it applies to any use-case where you need to identify the images that are available to your subscription.

These are the parts of the template that I need to populate with the answer, namely Publisher (most significant part of the sort), Offer, and Sku. Right now their default values are those in blue in the json extract.

"imagePublisher": {
 "type": "string",
 "defaultValue": "MicrosoftWindowsServer",
 "metadata": {
 "description": "Image Publisher"

"imageOffer": {
 "type": "string",
 "defaultValue": "WindowsServer",
 "metadata": {
 "description": "Image Offer"

"imageSKU": {
 "type": "string",
 "defaultValue": "2012-R2-Datacenter",
 "metadata": {
 "description": "Image SKU"

But as said I want a set that gives me some flavour of SQLServer 2016. Getting the right values is not as simple as running a built-in Azure PowerShell function. Oh no. There are some hoops to go through.

Once you have logged into your subscription, these are the steps:

1. Find the publisher. But how do I know which is the publisher I need? For me that is quite easy as it is usually some variation on Microsoft. But using the Out-GridView PS function will help.  I also need to specify a location. So we can start with this:

Get-AzureRmVMImagePublisher -Location uksouth | Out-GridView

Looking at the result of that below, after playing with the search filter, then I see my likely Publisher is [MicrosoftSQLServer].

So I can refine my query to this and stick the object output in a variable:

$publisher = Get-AzureRmVMImagePublisher -Location uksouth | ? {$_.PublisherName -like "*microsoftsql*"}

2. Find the offer.

Get-AzureRmVMImageOffer -Location "uksouth" -PublisherName $publisher.PublisherName

3. Find the SKU

Now I have my likely offer (SQL2016SP1-WS2016), my publisher (MicrosoftSQLServer), and now I need the sku:

Get-AzureRmVMImageSku -Location "uksouth" -PublisherName "MicrosoftSQLServer"  -Offer "SQL2016SP1-WS2016"

With those 3 answers (plus the location, which also comes into play), I can update the json:

The steps to deploy the template will be another post, but right now, as I chose Express, this is what I see in the created VM:


Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

SQL Server Management Studio (SSMS)

This is now a) free, b) no longer part of the SQL Server media. Get it here.


(Right now WordPress image upload seems to have  a glitch where it uploads in reverse order of capture, so read these pictures bottom to top)

SqlServer: dropping a subset of databases

While the [exec sp_MSforeachdb] way is attractive because it is so terse, it’s probably unsupported.

So this is a better path I think, and I thank this person [Pellared] for doing the work. For my purposes, I have just added in that I want to be more selective and to restrict the dropping to databases which start with a given prefix.

We start with the db explorer in this state:

We run this:

and we end up with this, i.e. the databases starting with [test_] have gone:

declare @command nvarchar(max);
set @command = '';

select @command = @command
+ 'alter database [' + [name] + '] set single_user with rollback immediate;' 
+ 'drop database [' + [name] +'];'
from [master].[sys].[databases] 
 where ( [name] not in ( 'master', 'model', 'msdb', 'tempdb')) and ([name] like 'test_%')

select @command;
execute sp_executesql @command;


SqlServer 2016: Developer Edition is free

… as is Management Studio, which is now completely separate from the Engine ISO.


SSMS is here, and SqlServer is here.

There pair are respectively about 1 and 3GB.

This is a working command line to install SQL (this one assumes that the ISO is mounted as F:


Azure RM: get an Image from an ImageFamily and use it

$family = "SQL Server 2014 SP1 Enterprise on Windows Server 2012 R2"
$image=Get-AzureVMImage | where { $_.ImageFamily -eq $family } | sort PublishedDate -Descending | select -ExpandProperty ImageName -First 1


Returns for example:


And now say I want to a VM that is running SQL Server 2016. This is the (or at least a) question to ask (after some initial selects to test the water):

$image2016 = Get-AzureVMImage | where { $_.ImageFamily -eq “SQL Server 2016 RTM Enterprise on Windows Server 2012 R2”}

And that returns me:


Below is the history give or take I used to get there (btw, only just discovered that e.g. [ Set-Clipboard] does not truncate output whereas [clip]does. For example:

Get-History | select -Unique | Set-Clipboard

azure login
cd "E:\gitrepos\AllThingsAzure\AzureCLI"
$global:password = "whatsthis"
$family = "SQL Server 2014 SP1 Enterprise on Windows Server 2012 R2"
$image=Get-AzureVMImage | where { $_.ImageFamily -eq $family } | sort PublishedDate -Descending | select -ExpandProperty ImageName -First 1
Get-AzureVMImage | where $ $_.label -Like "*2016*"
Get-AzureVMImage | where { $_.label -Like "*2016*"}
Get-AzureVMImage | where { $_.label -Like "*sql*2016*"}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"} | select {$_.Label}
function prompt{}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"} | select {$_.Label, $_.ImageName}
$image2016 | Format-Table
$image2016 | Format-List
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*Enterprise*R2"} | select {$_.Label, $_.ImageName}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*Enterprise*R2"}
$image2016 = Get-AzureVMImage | where { $_.ImageFamily -Like "SQL Server 2016 RTM Enterprise on Windows Server 2012 R2"}
$image2016 = Get-AzureVMImage | where { $_.ImageFamily -eq "SQL Server 2016 RTM Enterprise on Windows Server 2012 R2"}
Get-History | clip
Get-Help clip
alias clip
Get-History | Set-Clipboard
$x = Get-History
$x = (Get-History | Set-Clipboard)
$x = Get-Clipboard
alias get-clipboard
alias get-clipboard gclip
New-Alias -Name gclip -Value Get-Clipboard
gclip | unique
$x = gclip | unique 
$x | Set-Clipboard
Get-History | select -Unique | Set-Clipboard