• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

MS Sql Server - Export SQL

I'm a Linux/MySQL person - but I have been given an assignment to grab some data off of an IIS environment to migrate some data.  I'd like to export a few tables as raw SQL - but since it's off a live/production site I'm cautious about it.

In Enterprise Manager - when I drill down to the database/tables / right-click on the table - I see Generate SQL.

Is that the option I want to use - and how do I use it?  Is it in any way destructive to what exists?  And if that's *not* the way to do it - how do I export the SQL for the table?  :)

Thanks in advance!
1 Solution
PadawanDBAOperational DBACommented:
That will indeed get you the DDL definition for the table.  Just generate it to a new query window and you should be fine to take that and run.  Are you looking to get the data along with it or just the table definition? (getting the data would require different methods)

Edit:  Although... You mention Enterprise Manager.  Is this SQL 2000 ?
Deepak ChauhanSQL Server DBACommented:
Which version of sql server you are using.

Enterprise manager available in sql 2000.
You can use DTS wizard to expert data.

SQL 2005 and higher version.

You can use Import and export data wizard. Just right click on database > Task > Export Data, other steps are self explainatory.
erzoolanderAuthor Commented:
How do I tell what version I'm using?  When I get info about SQL Server Enterprise Manager - the version is 8.00.2039.

SQL-DMO Version. 8.00.02
ODBC Version 03.52.0000

As for the import/export wizard - I don't see a .sql option for the output types.  There's flatfile - which can either be a text or a CSV.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Deepak ChauhanSQL Server DBACommented:
You are using SQL server 2000.

Using DTS or Import\export Wizard you can only download data from the SQL tables to text files or CSV files or excel file.

If you want to generate object definition script of full database you can use generate script wizard by right click on the database.

If script require only for one or two objects you can just right click on that object (tables) and generate the script.

Here is the microsoft link explaining step by step how to generate script.

erzoolanderAuthor Commented:
Yep - saw that.  The one line that gave me pause was:

Generate the DROP <object> command for each object so that a DROP statement is added to the script for each object to be scripted. This is selected by default.

Caution  When executed, this causes any existing objects in the database (where the script is executed with the same name as objects listed in the script) to be deleted first.

Should I deselect that option?  I don't want it purging data that's in the live database.  I just want a SQL file representing what's in there.
Deepak ChauhanSQL Server DBACommented:
yes you can deselect that.

And i would like to suggest one more option. If you want otherwise only above is your requirement that is ok.

you can take a backup of whole database and save the backup file to any safe location.  
This will be a .bak file containing everything whatever in the current database.
Vitor MontalvãoMSSQL Senior EngineerCommented:
erzoolander, do you still need help with this question?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now