Now when I want to try something very quicky or with small databases I often use SQL Management Studio 2012 to import and export databases. Although this might not work with those huge 100 GB databases, it’s still a nice solution. If you right click on a database you can choose to export this database as a Data-tier application (which creates a bakpac file, often referred to as an ‘MSI’ containing the logical contents of your database).

The same goes if you right click your server, you can choose to import a Data-tier application which will create a new database on your server.

Backup and restore with PowerShell

The wizards in SQL Management Studio 2012 are very useful, but there will be times when you’ll need to do this on a regular basis. Since the export and import wizards of SQL Management Studio are referencing .NET assemblies, we can re-use these assemblies in a PowerShell script to automate the export and import. Here are the scripts:

Backup-SQLAzureDB.ps1

This is a very simple script allowing you to specify the connection string of your SQL Azure server, the database you wish to export and the output filename for the bacpac file. Note that this assumes that you have installed SQL Server Management Studio 2012 under: C:\Program Files (x86)\Microsoft SQL Server (if this isn’t the case you can simply specify the optional -SqlInstallationFolder parameter with the correct path).

Restore-SQLAzureDB.ps1

This script is very similar to the previous script. But instead of calling the BackupBakpac method this script calls the ImportBakpac method allowing you to use a local *.bacpac file to create a new database.