Category: Tooltip

Create and Export a bacpac file to Azure Storage

Next in my Azure database series is the bacpac file, Azure works with bacpac files, we can up- and download to azure. We need a storagespace in order to up and download files.

We start creating storage space. This may come in handy when we need file tranfer, and again you can access the files from your local device.

To acces your storage account, you need the access keys, which are generated when creating the storage account. On the footer of the page, where the red arrow is visible in the screenshot below, you can open up and acces the keys.

This key you need for the net step, we will download a windows Azure storage explorer to be able to acces the files. Azure has a broad choise in downloads for this option. I downloaded the Azure storage explorer from codeplex storage explorer.

Now I will walk you through on how to create a bacpac file on your local database. Choose the Export Data-tier Applicationbacpac1

Now we have to options, you can create the bacpac file locally and upload it with the storage explorer or link it to azure directly, we choose tthe latter. Fill in the storage credentials we just created by connecting the storage account with the access keys. Name a container and filename and thunderbirds are GO!




Connect your Azure database from your local SSMS #Error 40615

Ofcourse we wanna access our fresh imported database on Azure locally!

And this is so easy if you just follow these simple steps.

In order to be able to access the database located on your Azure cloud, you have to know the Azure address, which you can find on the Windows Azure Management Portal. Click the database icon and open up the database. Here you will find the connectionstring.


Next step is to make sure Azure knows your device by adding it to the trusted devices in Azure, if your device is unknow to Azure it will bring you the following message:1error-whenlocal_connection

you can add the device by going to the database icon in Azure and click the CONFIGURE tab. Here you add and save your device IP.2local_adTosavelist

Now you can login to your Azure cloud with the credentials you made while creating the database on Azure.
3Local_db connect

SQL database migration to Microsoft Azure with codeplex

sql-database-windows-azureThere are multiple ways to migrate your database to the Azure cloud. Today, I am testing this sql azure plugin by codeplex. I unzipped it to my local folder and run it as admin.


Now fire up the SQLAzureMW.exe and the script wizard starts up, which is very intuitive, choose the available options, In my case, I will migrate from SQL database to Azure SQL Database. Fill in your database details and run the export. This might take a while depending on how big your SQL Database is. You can also make a selection of tables/views you want to export, or just export the whole database.


Azure2 Azure3   Azure4 Azure5 Azure6   Azure7

Make sure you fill in the correct details for your target server (you can find your sql connection data on your azure Database configure page.) Azure10  Azure11

Now the database will be brought to the Azure cloud, again this might need some time, but you can see the progress on screen.

Azure11 Azure12


I'm a big fan of this sql azure plugin by codeplex , Like i said it's very intuitive and gives you several options to work with your local and azure data.

Move SQL Tempdb files – Common errors

Do you keep getting errors and timeouts when doing queries with sort?
My first guess wil be to check out the location of my tempdb files, is the disc space running out?db

Run query on the correct database
EXEC sp_helpfile GO
Tip: you can also view it under properties.

USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\DATA\tempdb.mdf'); GO


ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\DATA\templog.ldf'); GO

Restart sql services: 'stop and start'

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

rerun the sp query to verify the correct location fort he tempdb files
Now delete the old files or in my case rename them before finally remove them.
Next we are gonna change the properties for the autogrowth.
Error cant acces properties of tempdb:


Important Note on common ERRORS: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

Quickly build a Windows lab with VirtualBox, Packer, Vagrant and Chocolatey


I'm preparing a Powershell training for work and I needed an easy lab with virtual machines which I can easily distribute among the students. It should obviously all be Windows based.

So I started out working with Windows 10 and Vagrant to create a virtual lab, as I did on my Mac and Ubuntu box before, and I am very happy and (surprised) to announce that it works very smoothly on Windows. And I used Packer to create a Windows base box for Vagrant.

So I had great fun setting up my training lab and it would be a shame to keep it all for myself. So here goes. The Buttonfactory goes Devops!

Install Windows 10 Technical Preview and Chocolatey

Unfortunately, the preview of Windows 10 is not available anymore. If you look hard enough, I think it is possible to grab an iso somewhere.
By the way, the rest of these directions work also under Windows 7, 8 and 8.1.


Install Chocolatey
After installing Windows 10 I would advise to install Chocolatey. Chocolatey is like Ninite on the command line (or like apt-get for Windows if you will) and it is going te be very important in the future.

Open a command prompt as Administrator and paste this command in your terminal:

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((new-object net.webclient).DownloadString(''))" && SET PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin

(Or just head over to and follow the installation instructions).


Install Cmder
Then the first thing todo is install Cmder, a perfect replacement for the Windows terminal (which sucks a bit).
Fire up the command prompt as an admin and type:

cinst cmder -y

The Cmder binaries are installed in c:\tools. It is added to the path so you can just fire it up (start, run) and type Cmder.
Cmder also incorporates Msysgit which brings you, apart from Git, a light set of GNU tools as well like ls, grep, awk and vim, which I adore.

You can add another tab with a new console and tick 'Run as administrator' for convenience.
2015-07-19 14_17_18-ConEmu





And you can designate a tab for Powershell as well:
2015-07-19 14_19_59-Cmder







Install VirtualBox, Vagrant and Packer

This is also possible from Chocolatey.

cinst virtualbox vagrant -y

Add vagrant to the path. My path looks like this: C:\tools\cmder;C:\PROGRA~2\Oracle\VirtualBox;c:\HashiCorp\Vagrant\bin

The next steps to install Packer are:

  • Open Cmder
  • Type: mkdir \Vagrant
  • Type: cd \Vagrant
  • Type: git clone
  • Cd into packer-windows and see what has been downloaded
  • Now download packer and extract all contents to the packer-windows folder. (I think that is convenient)

Build a Windows Server 2012 R2 base box

This paragraph is based on this blog post from Rui Lopes.

We are going to edit the windows_2012_r2.json a bit to adjust it to our needs:

  • On line 38: set headless to false to see what is going on
  • On line 42: change the ssh_wait_timeout from 4h to 8h (to have enough time to install all updates)
  • On line 76: Remove the ./scripts/compact.bat (it takes ages to run) and the ./scripts/chef.bat (we are not gonna need it) lines.
  • You might want to change the answer file to change the Culture, but that can be done in the Vagrantfile later.

Now we are ready to build the box. Make sure you are in the c:\vagrant\packer-windows folder and type:

packer build -only virtualbox-iso windows_2012_r2.json

And now we wait. This takes forever. I'd suggest you go for a walk or perhaps to bed early. But the price is big!

Add the Windows Server 2012 R2 base box to Vagrant

When you return from your other extra curricular activity, you will see a box file is created in the c:\vagrant\packer\packer-windows folder.
You should add this box to Vagrant:

vagrant box add --name windows_2012_r2

This will also take a short time.

It will add the box to C:\Users\\.vagrant.d\boxes\windows_2012_r2\0\virtualbox:

2015-07-19 15_27_06-Cmder

You can basically get rid of the box file in c:\vagrant\packer\packer-windows now, or copy it to an USB drive or so because for now it is not longer needed.

Fire up the Virtual Server already!

  • Go to c:\Vagrant and create a dir for the server (mine is called DSC1)
  • cd into DSC1
  • Type vagrant init
  • Edit the Vagrantfile that this creates and make it to look like so:
    Vagrant.require_version ">= 1.6.2"
    $root_provision_script = <<'ROOT_PROVISION_SCRIPT'
    & $env:windir\system32\tzutil /s "W. Europe Standard Time"
    Vagrant.configure("2") do |config|
        config.vm.define "DSC1" = "windows_2012_r2"
        config.vm.hostname = "DSC1"
        config.vm.provider :virtualbox do |v, override|
            v.gui = true
            v.customize ["modifyvm", :id, "--memory", 2048]
            v.customize ["modifyvm", :id, "--cpus", 2]
    "private_network", ip: ""
        config.vm.provision "shell", inline: $root_provision_script
  • Save it
  • Type: vagrant up

When it is finished, you type vagrant rdp and you can log on to your box.
(Username vagrant, password vagrant).

2015-07-19 15_42_33-Quickly build a Windows lab with VirtualBox, Packer, Vagrant and Chocolatey _ Th

Now to halt the box: you type: vagrant halt.
To destroy the box: you type vagrant destroy.
And you can start again with a clean slate in seconds with vagrant up.

Create more folders for other servers, adjust the Vagrantfile (server name, IP address) and spin them up.

Extra: add Chocolatey to the Vagrant box

You can provision your Vagrant boxes with Chocolatey, and install Cmder and Notepad2 (or whatever packages you might need from the Choco repos). To do so, change the Vagrantfile like so:

Vagrant.require_version ">= 1.6.2"

$root_provision_script = <<'ROOT_PROVISION_SCRIPT'
#adjust the time zone:
& $env:windir\system32\tzutil /s "W. Europe Standard Time"

#install Chocolatey
iex ((new-object net.webclient).DownloadString(''))

#and install Cmder and Notepad2
choco install cmder notepad2 -y

Vagrant.configure("2") do |config|
    config.vm.define "DSC1" = "windows_2012_r2"
	  config.vm.hostname = "DSC1"

	        config.vm.provider :virtualbox do |v, override|
		        v.gui = true
		        v.customize ["modifyvm", :id, "--memory", 2048]
		        v.customize ["modifyvm", :id, "--cpus", 2]
			    end "private_network", ip: ""
      config.vm.provision "shell", inline: $root_provision_script