16

Is there any way to export a Microsoft SQL Server database to an sql script?

I'm looking for something which behaves similarly to mysqldump, taking a database name, and producing a single script which will recreate all the tables, stored procedures, reinsert all the data etc.

I've seen http://vyaskn.tripod.com/code.htm#inserts, but I ideally want something to recreate everything (not just the data) which works in a single step to produce the final script.

14

Try Sql Server Database Publishing Wizard. You may need to re-order the script to make it run in one shot though because of dependencies but it will include your schema and data.

6

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreing keys. If you combine it with that script for the inserts you'll get pretty much what mysqldump produces.

TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.

1

Hey Matt,
Try DBSourceTools.
It's designed to script out a Source Database, and re-deploy to a Target Database.
It scripts schema and data.
http://dbsourcetools.codeplex.com
- Nathan

0

@Matt

Yes, it doesn't export the data. That's why I mentioned that you should combine it with the script you suggested. This method does builds a script in the correct order though.

0

Ombelt make a nice tool for exporting MS SQL server DB's. www.ombelt.com

a lot like other DB's dump facilities.

Works for me.

0

I found SQL Dumper pretty useful. It's free so you can give it a try. It lets you choose the database tables and columns, views and even the results of custom queries as SQL insert statements.

-4

@Matt Sheppard Can you replace "SQL Server" by "Microsoft SQL Server", as "SQL Server" is generic and could refer to MySQL, PostgreSQL, etc.