Powershell your way to backing up SQL database to Azure

The script below shrinks the database and then backs it up to azure.

Azure storage accounts have an account name, and two passwords (known as primary and secondary access keys).

The storage account name forms a unique path for your storage. Say your storage accountname is joebloggs, then the path to your storage is


Files are not stored directly under the storage url. Instead they must be stored in containers which are a little bit like directories. The reason we don’t call these directories is because an Azure storage account can also hold table storage for tabular data.

Firstly, you’d probably want to shrink your database


# Use the logical name, i.e. excludes directory and file extensions
Invoke-SqlCmd -Query 'USE sampledb'
Invoke-SqlCmd -Query 'DBCC SHRINKFILE (''sampledb'')'
Invoke-SqlCmd -Query 'DBCC SHRINKFILE (''sampledb_log'')'

Invoke-SqlCmd -Query 'ALTER DATABASE sampledb SET RECOVERY FULL'

Then you’ll need to save the login and access key to the Azure storage in SQL Server. This is known as a credential

New-SqlCredential -Name azure-joebloggs -Identity joebloggs -Secret 'Your-Very-Long-Access-Key'

Warning: the following operation will fail if the file already exists in the storage container

Backup-SqlDatabase -ServerInstance $Env:COMPUTERNAME -SqlCredential azure-joebloggs `
    -BackupFile 'https://accountname.blob.core.windows.net/containername/sampledb.bak' `
    -Database 'sampledb'

# this will overwrite the original back up file
Backup-SqlDatabase -ServerInstance $Env:COMPUTERNAME -SqlCredential azure-joebloggs `
    -BackupFile 'https://accountname.blob.core.windows.net/containername/sampledb.bak' `
    -Database 'sampledb' -ReplaceDatabase

# this will create a new backup file named automatically by SQL Server
Backup-SqlDatabase -ServerInstance $Env:COMPUTERNAME -SqlCredential azure-joebloggs `
    -BackupContainer 'https://accountname.blob.core.windows.net/containername' `
    -Database 'sampledb'

To restore the database, just change “Backup-SqlDatabase” to “Restore-SqlDatabase”

Restore-SqlDatabase -ServerInstance $Env:COMPUTERNAME -SqlCredential azure-joebloggs `
    -BackupFile 'https://accountname.blob.core.windows.net/containername/sampledb.bak' `
    -Database 'sampledb'

It is not obvious how one can change the SQL Server authentication mode on PowerShell. This one works:

# Enable SQL Authentication
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') <serverName>
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
Stop-Service -ServiceName MSSQLServer
Start-Service -ServiceName MSSQLServer

Alternately, you can do this using PowerShell Paths. It is somewhat prettier.

Import-Module sqlps -DisableNameChecking
$s = (Get-Item SQLSerer:\SQL\<serverName>\<InstanceName>)
$.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
Stop-Service -ServiceName MSSQLServer
Start-Service -ServiceName MSSQLServer

Enabling ngrok to connect to IIS Express


ngrok http -host-header=rewrite localhost:19301

Why you might need this

If you want to test what your mobile site looks like using a real device, and the site is running locally on your PC using IIS Express and only accessible via localhost. ngrok is a 1 minute solution to make it accessible.

If you want to test a webhook while you are still developing. For instance, SendGrid will call webhooks that notify you when an email bounces.

Step 1: Download ngrok, and unzip into


Step 2: Run your web app and note what port it runs on

Step 3: start


run the command below, replacing 19301 with the port your web app is running on

C:\Users\Chui> cd \bin
C:\bin> ngrok.exe http -host-header=rewrite localhost:19301

Step 4: Note the URL that ngrok shows on the command prompt, it typically looks like, and you can visit either the http or https url from your mobile device. Note that there is no security. Your app is publicly accessible, and you should secure it appropriately.

Forwarding                    http://ed273612.ngrok.io -> localhost:19301
Forwarding                    https://ed273612.ngrok.io -> localhost:19301


What is ngrok, and how does this work?

ngrok has a server that is publicly accessible. When you run ngrok locally, it connects to the publicly accessible server listening for commands. When a browser visits the url on the public website, ngrok.exe calls the website running on your computer and forwards the page back to the browser.

What about url reservation using netsh http add urlacl url=http://89341ab3.ngrok.io/ user=Everyone? Or changing IISExpress application.config ?

This isn’t necessary because the command aboves gets ngrok to rewrite the Host header so that the request appears as if it is meant for localhost.


Creating a Message Only window using WPF

Classic Win32 applications use a Window as basic unit of abstraction, including using it for Inter Process Communication. For example, a message only window is hidden, and we only listen to to for messages that are posted from elsewhere. To create a message only window, it must have its parent HWND set to HWND_MESSAGE (i.e. IntPtr(-3)). Then custom messages can be sent as long as the message id falls within the bounds specified here.

While WPF does away with Windows, the interop library provides the basic building blocks for us to create Win32 windows.

/// <summary>
///     Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
    private readonly IntPtr _sourceHandle;

    // Valid ranges here:
    // https://msdn.microsoft.com/en-us/library/windows/desktop/ms644927(v=vs.85).aspx#app_defined
    private const uint CustomMessage = 0x8801; 

    public MainWindow()

        _sourceHandle = CreateMessageOnlyWindow();
        Button1.Click += SendMessageToMessageOnlyWindow;

    private IntPtr CreateMessageOnlyWindow()
        IntPtr HWND_MESSAGE = new IntPtr(-3);
        var source =
            new HwndSource(new HwndSourceParameters() { ParentWindow = HWND_MESSAGE});
        return source.Handle;

    private IntPtr WndProc(IntPtr hwnd, int msg, IntPtr wparam, IntPtr lparam, ref bool handled)
        if (msg == CustomMessage)
                new Action(delegate { Button1.Content = lparam.ToInt32().ToString(); }));
            handled = true;

            return new IntPtr(20);
        return IntPtr.Zero;

    private void SendMessageToMessageOnlyWindow(object sender, RoutedEventArgs e)
        var result = SendMessage(_sourceHandle, CustomMessage, IntPtr.Zero, new IntPtr(DateTime.Now.Second));
        Contract.Assert(result.ToInt32() == 20);

    [DllImport("user32.dll", CharSet = CharSet.Auto)]
    private static extern IntPtr SendMessage(IntPtr hWnd, UInt32 Msg, IntPtr wParam, IntPtr lParam);

Managed Metadata in Sharepoint 2013 DisplayTemplate

Applicability: Sharepoint 2013, Sharepoint Online

Display Templates in Sharepoint allow individual content types to be displayed differently in Sharepoint Content Search results (template files are here _catalogs/masterpage/Display Templates/Content Web Parts. In addition, since each content type can have different columns, the display templates allow properties to be remapped to rendered fields as well.

Display templates are by default deactivated, and requires a manual activation step in the Site Settings, under Publishing Infrastructure.

Each display template has an ugly looking metadata segment that makes it looks like MS Word-generated HTML.

<mso:ManagedPropertyMapping msdt:dt="string">
  'RenderPropertyName'{Web Part Label}:'PublishingImage;RefinableText01;...',
  'Link URL'{Link URL}:'Path',
  'Line 1'{Line 1}:'Title',
  'Line 2'{Line 2}:'Description',
  'Line 3'{Line 3}:'',

The key take away here are:

  • when rendering in the display templates, use RenderPropertyName;
  • when presenting the property to be customised in the Web Part – use Web Part Label;
  • to set the default properties, set them in the final segment.

The actual rendering appears to be done on the client-side using a custom Javascript templating language that is reminiscent of PHP.

var propertyValue = $getItemValue(ctx, "RenderPropertyName");
_#= propertyValue =#_

Hat tip to @cann0nf0dder for working this out!

Clean Code with AngularJS on Windows – Part 2

This is Part 2 of a series on AngularJS testing.

Our basic test case is a user registration service.

using a TDD philosophy, we start with our failing test.

// file: tests/userReg.js
describe('Test Suite', function() {

  var scope;

  // before each test is run, load the userReg module

  // before each test is run, sets the scope
  beforeEach(angular.mock.inject(function($rootScope, $controller) {
     scope = $rootScope.$new();

  // single asynchronous unit test
  // by including 'done' argument, the test will not complete
  // until done() is called.
  it('service should asynchronously return true', function(done) {

    // inject() is kind of like a DI constructor
    // note that userRegistrationService is registered as a 
    // factory in the userRegApp module
    angular.mock.inject(function($q, userRegistrationService) {

        // mock the userRegistrationService.usernameExists()
        var deferred = $q.defer();
        spyOn(userRegistrationService, 'usernameExists').and.returnValue(deferred.promise);

            .then(function(result) {




We also need to configure karma.conf.js to load angular.js, angular-mocks.js, our source files and test files.

// Karma configuration

  // list of files / patterns to load in the browser
  files: [

Launch karma to test

$ node_modules/karma/bin/karma start karma.conf.js

Karma should spit out long error messages such as this:

 Error: [$injector:modulerr] Failed to instantiate module userRegApp due to:
 Error: [$injector:nomod] Module 'userRegApp' is not available! You either misspelled the module name or forgot to load it.

Create a new file called app/userRegistration.js

angular.module('userRegApp', [])
.factory('userRegistrationService', ['$http', '$q', function($http, $q) {
  return {
    usernameExists: function(userName) {

      // .NET developers may recognize deferred as
      // being the equivalent of TaskCompletionSource
      var deferred = $q.defer();
      $http.get('/webapi/v1/usernameExists/' + userName)
        .success(function(payload) { deferred.resolve(payload.data); })
      return deferred.promise;

Karma should report that the unit test passed.

INFO [watcher]: Added file "...../ngtest/app/userRegistration.js".
Chrome 39.0.2171 (Windows 8.1): Executed 1 of 1 SUCCESS (0.036 secs / 0.032 secs)

Clean Code with AngularJS on Windows – Part 1

Writing clean, testable code with AngularJS requires a specific toolchain to be present, these are:

  • npm (package manager for back end)
  • karma
  • bower (package manager for front end – to install angularjs components)

This exercise takes approximately 5 minutes (for packages to download). It installs the basic components necessary.

If there’s an existing project, use npm to automatically install all dependencies specified in package.json. Use a special switch so that binaries are built using Visual Studio 2013 (otherwise defaults to VS2010)
$ npm install --msvs-version=2013

Create an empty package.json file

file: package.json

Install Karma

# install karma
$ npm install karma --save-dev --msvs-version=2013
#install plugins
$ npm install karma-jasmine karma-chrome-launcher --save-dev --msvs-version=2013
#configure karma
$ node_modules/karma/bin/karma init karma.conf.js
Which testing framework do you want to use ?
Press tab to list possible options. Enter to move to the next question.
> jasmine
Do you want to use Require.js ?
This will add Require.js plugin.
Press tab to list possible options. Enter to move to the next question.
> no
Do you want to capture any browsers automatically ?
Press tab to list possible options. Enter empty string to move to the next question.
> Chrome
What is the location of your source and test files ?
You can use glob patterns, eg. "js/*.js" or "test/**/*Spec.js".
Enter empty string to move to the next question.
Should any of the files included by the previous patterns be excluded ?
You can use glob patterns, eg. "**/*.swp".
Enter empty string to move to the next question.
Do you want Karma to watch all the files and run the tests on change ?
Press tab to list possible options.
> yes
Config file generated at "....\karma.conf.js".

Install Bower

# install bower (front-end package manager)
$ npm install -g bower --save
# creates bower.json
$ bower init
# install angular and angular mocks
$ bower install --save angular angular-mocks

git-tf notes

Working with Xamarin iOS with an existing project means there’s a new tool to pick up. The source code repository is on TFS, while my development machine is a Macbook Pro.

Luckily, Microsoft has provided a java based command line client called git-tf.

One of the sub-requirements is that I need to fork the entire TFS repository to git, as my development build machine will be building off a git repository. I’ll need to regularly merge back changes into the TFS.

The first step is to take a full history of the TFS repository.

$ git-tf clone --deep https://tfs2012.mydomain.com/tfs/mycollection $/myproject/Trunk MyLocalDir
username: chui.tey

Next, push it into my remote git repository on Bitbucket.

git remote add bitbucket git@bitbucket.org:/teyc/myproject.git