Today there are a few good options to backup from SQL Azure, restore to SQL Azure or even migrate to SQL Azure:
- Announcing SQL Azure Import/Export Service Now in Production (with client)
- Enzo Backup for SQL Azure
- Red-Gate SQL Azure Backup
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:
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).
.\Backup-SQLAzureDB.ps1 -ConnectionString “Server=tcp:….database.windows.net,1433;Database=MyDb;User ID=something@server;Password=pwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;” -DatabaseName “MyDB” -OutputFile “C:\backup.bacpac”
At the end of the backup the script will show how long it took:
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.
The syntax is almost the same:
.\Restore-SQLAzureDB.ps1 -ConnectionString “Server=tcp:someServer.database.windows.net,1433;User ID=someUser@someServer;Password=MyPwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;” -DatabaseName “SomeRestoredDb” -InputFile “C:\BackupFile.bacpac”
Both scripts are available on GitHub.