Generating Scripts for SQL Azure

  • Article

[This article was contributed by the SQL Azure team.]

When moving a SQL Server database to SQL Azure the first step is to generate a script that will transfer the schema of your SQL Server database to SQL Azure. SQL Server Management Studio 2008 R2 easily does this with a newly added advanced setting that allows you to target SQL Azure as you engine type.

Here is the step by step walk through:

1) Open SQL Server Management Studio 2008 R2

2) Connect to your SQL Server that contains the source database that you want to transfer.

3) Right click on the database and choose Tasks… | Generate Scripts…

clip_image002

4) This will start the Generate and Publish Scripts dialog. Skip the Introduction wizard page by clicking Next > .

5) This will take you to the Choose Objects wizard page.

clip_image004

6) Choose Script entire database and all database objects and click on Next > .

7) This will take you to the Set Scripting Options wizard page.

clip_image006

8) Choose Save to new query window, then click on the Advanced button. This will bring up the Advanced Scripting Option dialog.

clip_image008

9) Scroll down until you see the option called Script for the database engine type from the drop down choose SQL Azure Database.

10) Click Next > .

11) Click Next > to skip the summary wizard page.

12) Once Generate and Publish Scripts dialog is done querying your database, you can click Finish and a query that is enabled for SQL Azure will appear in your query window.

13) This new query window in SQL Server Management Studio will automatically be connected the source SQL Server.

14) Right click in the query window, from the drop down menu choose Connection | Change Connection…

clip_image010

15) Now connect to the SQL Azure server where you database is located. You can choose the database using the Options >> button.

clip_image012

16) Execute the generated script in SQL Server Management Studio and your schema will be created on SQL Azure.

In order to use SSIS or BCP to transfer your SQL Server data to SQL Azure you need to have the schema is place on SQL Azure including your clustered indexes. Another option to upload data is changing the settings on the Generate and Publish Scripts dialog to include data generation. INSERT statements will be added to the script increasing the overall size of the script – this only works well for small databases.

Do you have questions, concerns, comments? Post them below and we will try to address them.