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!
LVL 2
erzoolanderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ?
0
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.
0
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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.


https://technet.microsoft.com/en-us/library/aa197132(v=sql.80).aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
erzoolander, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.