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.
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 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
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.
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.
Nice to learn about this ! I guess if a GUI version of it is let out then it would be even better.
Thanks and Regards,
Hardik Shah: The GUI Version is the SQL Azure Portal, found here: http://sql.azure.com. There you can create and drop databases using a web interface. You can also create and drop a database via SQL Server Management Studio 2008 R2, by executing the Transact-SQL above.
There is a free tool "SQLS*Plus" which is an SQL*Plus for SQL Server. Works with SQL Server 2000/2005 and 2008
Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, etc - light years better than isql, osql or sqlcmd
I downloaded SQLS*Plus from http://www.memfix.com
Could I run a script to create all the tables in the database and populate them?
Typo alert: "-i –N CreateDatabase.sql" s/b "–N -i CreateDatabase.sql"