Is there any way to export a Microsoft SQL Server database to a 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 and reinsert all the data etc.
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 entitled "Set Scripting Options", click on the button (on the right of the window) labelled "Advanced" and modify the option "Types of data to script" and choose "Schema and data".
TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.
The recommended solution only works on sql 2000 and 2005. If you're looking to do this on SQL 2008,
You can do it with SQL 2008 without any other plug ins. Right click on the database and select "Tasks -> Generate Scripts...". Select the database and what you want to back up. Click next and set the "Script Data" to true.
SQL Server Database Publishing Wizard seems indeed to be the best way to do it. The problem with this is that it does not seem to run on Windows 7. I had to use my old computer to use it. On the positive side, it works with older SQL Server versions like 2000.