How to generate SQL Scripts from SQL Azure.

Question

Hi,

I had recently lost my backup copy of my SQL Scripts which i had previously use
to create the Tables in my SQL Azure account.

Right now, i'm in an urgent need to retrieve the sql scripts.
I've tried SSMS but i can unable to call the "Generate Scripts" functions on the SQL Azure account.

Does anyone know of any alternatives to retrieve a SQL Scripts of the structure in my SQL Azure Account?

Really thanks alot..

Answer 1

The only thing I can figure out at a glance is SSIS (SQL Server Integration Services) project. It works with Azure with respect of SQL Azure being a ADO.NET Target. I haven't tried it as Source but you can give it a shot while searching for other solution.
Also I have heard that current CTP of SQL Azure supports BCP copy option so you should be able to copy your data with BCP tool.

Hope that this will help you.

Answer 2

Well, I just tried the SQL Azure migration wizard, but it will fail if you try to connect to SQL Azure at first. It works only from SQL Server to SQL Azure, not vice-versa.

Answer 3

Hey Anton Staykov,

thanks! i had previously tried with SQL Migration wizard and the same happened to me..
I'll try to think of other alternatives then..

thank you so much for your help =)

Cheers!

Answer 4

Hi,
I think SSIS might help in this case. I'll give it a try later and will come back here for updates. This is actually a very interesting subject that needs attention.

Answer 5

Hello again,
So I tried with SSIS - it actually works, but you have to manually add all tables in a DataFlow Task. You have to create a ADO.NET Source (Azure) and ADO.NET Destination (Local), where the local will be a blank data base and you will choose the option to create tables. It will automatically create a Create DDL statement. It is a bit time consuming. And these actions will help you create tables and transport data. These actions however will not create any indexes/view/foreign keys/procedures/functions. It is a start.
I tried also a "Transfer DataBase Task" with total failure.
I also gave a try with "Transfer SQL Server Object"  but it requires SMO (Sql server Management Objects) which are currently not supported on Azure - so, no success again.

Answer 6

It looks like this might be the best approach for now.  I tried reverse engineering the TSQL executed by Management Studio to script the entire database, but SSMS still uses some TSQL that is unsupported by SQL Azure.