Reach for the Sky… Script a Database for SQL Azure

While there are many different ways to get your database up and running into the cloud. You could read up on SQL Azure Sync Framework in my post titled “Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework”.

However, nothing feels more comfortable to a developer than something that familiar. After a little bit of investigating while preparing for my talk at Confoo on SQL Azure, I managed to find a post on the MSDN website that Explains what is needed in order to use the Generate Scripts Wizard in SQL Server Management Studio.

Create the Transact-SQL Script

  1. In Object Explorer, right-click the database, point to Tasks, and select Generate Scripts.

  2. In the Script Wizard dialog box, click Next to get to the Select Database step. Select School, select Script all objects in the selected database, and then click Next.

  3. In Choose Script Options, set the following options:

    • Convert UDDTs to Base Types = True
    • Script Extended Properties = False
    • Script Logins = False
    • Script USE DATABASE = False
    • Script Data = True

    SQL Azure does not support user-defined data types, extended properties, Windows authentication, or the USE statement.

  4. Click Next, click Next, and then click Finish. The Script Wizard generates the script. Click Close when the script is completed.

  5. In the generated script, delete all instances of "SET ANSI_NULLS ON".

  6. Each CREATE TABLE statement includes a "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" clause. Delete all instances of that clause.

  7. Each CREATE TABLE statement includes the "ON [PRIMARY]" clause. Delete all instances of that clause.

The reason you need to apply these changes to the script is that SQL Azure currently doesn’t support all the features of the currently release of SQL Server 2008.  There are plans to start to incorporate some of the features that are in this outline, including the USE statement.

Hopefully this will make your life easier on your move to the cloud. Until then, Happy Coding!