Is there a way to "export" an entire SQL Server database (schema and data) to text?

Question

On a per-object basis, SQL Server Management Studio has the option to script the object to clipboard, file, etc. Is there a way (at the database level) to do a "1-click" export of ddl AND data to a text file? Ideally I'd like to select a database and create a big text file containing the ddl for all objects plus the data in all tables. I see the data export wizard (dts/ssis) as a very different thing which really isn't what I'm looking for.

Is this capability available either in SQL Server Management Studio or in the Visual Studio Database Edition?

Solution

Yes, in SQL 2008 you can script objects and data.

Right-click on database in Management Studio, Tasks, Generate Scripts ....

Go through the wizard and make sure to check "data" which is false by default.

It is not any easier to find in SQL 2012:

Right-click on database in Management Studio, Tasks, Generate Scripts.
On the "Set scripting options" tab click on Advanced, then select "data only", or "data and schema" for "Types of data to script" (in the General section).

Comments

[Patrick Karcher]

beat me to it. Yep, it's hard to find. I've used it a few dozen times, and still have a hard time finding it.

[Emilio]

Wow, look at that! My eyes have always zeroed-in on the "Script Database As..." menu which doesn't give the option so I never thought to look further. I'd call this poor user interface design!

[Milimetric]

Anybody know if there's a way to do this programmatically?

[Cristian]

I'm also wondering about automating the export with custom options

[Daniel Compton]

How do you do this in SQL 2012?

[cdonner]

@Daniel: no different in 2012. On the "Set scripting options" tab click on Advanced, then select "data only" for "Types of data to script" (in the General section). I will add this to my answer.