How to export data to a SQL file?

[sjh]

Hi,

Is there a way in SQL server to export data in all tables to a SQL Insert script (and in correct dependency order)? I looked into Enterprise Manager and Query Analyzer, but I don't see any tools for doing such task.

Thank you in advance for your help!
J

 

[adamroof]

yes, its called backup and restore!
get the .bak file, then restore as different database name on same db server or other.

 

[vongrunt]

Either backup/restore (this overwrites entire database), or dtswiz.exe (Copy Objects option - tricky, be careful!). There are no tools/features that dump data only into SQL files (a la mysqldump -d, right?)... unless you make one or google for it

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[sjh]

I will clarify my original question.

We are doing development, and I want to "rebuild" the database each time (create tables and then insert the data) we have a new application build. We are still in the design phase, so the database may change. I don't think backup and restore would work well in my case. Any other ideas??

Thanks!
J

 

[fredericofonseca]

Create a new database with the rebuild info (base DB).

Everytime you need to rebuild, issue a drop of the affected tables (can be a sql script), and then do a copy server objects DTS package to copy your objects from the (base DB) into the development DB.

Easy to create the required processes, easily changed also.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mrdenny]

You can script the database in Enterprise Manager. You can use Query Analyzer to run that script. Then use BCP to export and reimport the data.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com