2

I'm used to MySQL and PHPMyAdmin - I had to switch over to MSSQL for an ASP.net project, and I'm having tons of trouble. I'm using the express version of SQL 2008, with SQL Server Management Studio. The following are 2 questions I've been struggling with for a while:

1) How do I export the DB schema for the database? The table structure, etc.?

2) How do I export all the data in the database?

Ideally I'd like to have a .sql file that can be run wherever I need the schema or data duplicated, for example a co-worker's computer for a shared project, or online when the project is being hosted.

Thanks!

stringo0
  • 2,720
  • 7
  • 38
  • 52

1 Answers1

2

1) How do I export the DB schema for the database? The table structure, etc.?

INFORMATION_SCHEMA is your friend

 SELECT * FROM INFORMATION_SCHEMA.TABLES

http://www.mssqltips.com/tutorial.asp?tutorial=179
http://weblogs.asp.net/jgalloway/archive/2006/07/07/455797.aspx
http://preetul.wordpress.com/2009/06/09/sql-server-information_schema/

Otherwise, if you want something pretty looking, download the 14 day trial of SQL Doc (part of SQL Toolbelt) here: http://www.red-gate.com/products/SQL_Professional_Toolbelt/index.htm

"2) How do I export all the data in the database?"

In what form? .bak files are typically the most useful. http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups Or were you looking to move the data into MYSQL or Excel or some other program? If you want to move data to MYSQL check here: http://www.google.com/search?q=mssql+to+mysql

Albert
  • 3,639
  • 13
  • 43
  • 58
  • For the .bak files, does it export just the data? For example, if I want to copy over some new data I added to the db to another db, can I use the .back file? I'm assuming so. – stringo0 May 03 '10 at 19:31
  • For #1, the intention is to replicate the schema in another database - it'd be nice to be able to create a sql script or similar that I can just run on the other database to replicate the schema. – stringo0 May 03 '10 at 19:48
  • 1
    For what you're talking about for #1, the SQL Server Management Studio has GUI tools to transfer data from one db to another...i use them all the time. After installing, right click on the database and select 'Export Data' and follow the prompts from there otherwise, to transfer data via script do this: http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/ – Albert May 03 '10 at 20:53
  • to replace the schema i would just make a .BAK file, and restore it to a new database. that will bring over the schema and all the data. to do it via script right click on the database, 'Script Database As' then 'Create To' and that will generate all the Schema scripts – Albert May 03 '10 at 20:53