How to dump a Microsoft SQL Server database to a SQL script?

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.

Solution 1:

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 foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

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

Solution 2:

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.

If you havent got 2005 SQL XMO objects installed, you'll get an error when you run the wizard. You'll want the Microsoft SQL Server 2005 Management Objects Collection

Solution 3:

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.

Solution 4:

Not finding the right tool, I decided to create my own: a sqlserverdump command line utility. Check it out on http://sqlserverdump.codeplex.com/. It will recreate schema and data in a single step.

Solution 5:

Try DBSourceTools. It's designed to script out a source database, and re-deploy to a target database. It scripts schema and data.