Thursday, June 03, 2010

Creating a SQL Azure Database with SQLCMD

by Wayne Walter Berry via SQL Azure Team Blog on 6/3/2010 2:18:17 PM

Once you have your server allocated via the SQL Azure Portal you can create and drop databases from your desktop’s command line using sqlcmd.exe. This can be handy for clean slate testing where you want to create a database from scratch, unload a schema and then do some testing in a repeatable way.

One thing to note is that you want to target the master database when creating and dropping databases. It is also important to encrypt your connection using the –N parameter, you can learn more about why we encrypt in this blog post.

Creating a Database

First, you need to construct a .sql file that contain the Transact-SQL command to create your database, it should look something like this:

CREATE DATABASE Test

This will create you a 1 GigaByte database, the default size. There should be no other Transact-SQL statements in this .sql file. More about the CREATE DATABASE command in SQL Azure can be found on MSDN.

To execute this command from the command line where the file is named CreateDatabase.sql, use this command:

sqlcmd -SyourSeverdatabase.windows.net -UyouLogin@yourServer -PyourPassword -dmaster -i –N CreateDatabase.sql

The user name needs to be in the form: yourLogin@yourServer, where the login is the administrator login for the server on SQL Azure. You can get this information directly from the SQL Azure portal. Notice that the database targeted is master.

Dropping a Database

Dropping a database is down in much the same way. The script looks like this:

DROP DATABASE Test

More about the DROP DATABASE command in SQL Azure can be found on MSDN.

To execute this command from the command line where the file is named DropDatabase.sql, use this command:

sqlcmd -SyourSeverdatabase.windows.net -UyouLogin@yourServer -PyourPassword -dmaster -i –N DropDatabase.sql

Experimenting

Just a note when experimenting with these scripts: The minimum billing period for SQL Azure is one day; the 1 Gigabyte database costs $9.99 per month. That is roughly 33 cents a day. To create and immediately drop a database will cost you a minimum 33 cents. More on pricing can be found here.

Summary

The tools you are used to using with SQL Server work with SQL Azure, including sqlcmd.exe. Do you have questions, concerns, comments? Post them below and we will try to address them.