SQL Azure Data Definition backup

[Lightwave]

In Azure SQL I was wondering if anyone had tried to save the Create table/view generated SQL scripts in some way automatically to a linked ms access table from specified remotely linked SQL Azure database. I only need specified tables and views.. (I am talking about SSMS which has an option to generate the scripts as ALTER TO - CREATE on the main data defnition objects in a database.

Such that each table or view create statement SQL is in a separate record within an Access native table. I've had success in automatically transferring all data from remotely linked SQL Azure tables to local access tables and would kind of like to do the same except for the definition of the tables and views. As this seems to be a SSMS feature and I am proposing cutting out SSMS I may be completely unrealistic but interested in what people think.

I know there is the data tier export option and that seems to work somewhat for me but I like to have multiple ways of backing up and deploying backups. Plus I don't have full blown SQL Server on my local machine only express and I think its failing on complicated backups to redeploy backups and data tiers because of this... Plus the longer I can avoid the paid for service the cheaper it is!!!

For those interested this is what I use to dump remote tables into MS Access locally. It will loop through all linked tables and views so I only link those tables and views that I want dumped locally.

MS SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

Obviously you have the 2gb limit but at the moment I'm doing a lot of small complex systems where data quantity ain't the issue...

So in essence I link to a variable number of tables or views in SQL Azure.
Run a function and it loops through all tables and views and writes one SQL Create statement in its own record in a table for each table and view.

 

[Lightwave]

OK will add some notes in this thread

Something similar with Oracle

 

[Lightwave]

View table definition in TSQL

TSQL

 

[cheekybuddha]

SQLServer has no straightforward way of producing a CREATE TABLE statement (unlike eg MySQL where you can just issue: SHOW CREATE TABLE TableName;) - you have to extricate the data from the sys tables.

 

[cheekybuddha]

Here's a useful SO thread

 

[Lightwave]

Many thanks

 

[cheekybuddha]

If you have an Access db with linked tables to your SQLServer I wrote a very basic application a while back which can create DDL from a target db.

It was really designed for Access db's so you could extract DDL easily and post it to a forum like this. I haven't touched it since I made it so it's still full of very rough edges!

You can find it here if it might help you