Which is the simple way to export a database from SQL Server including all tables and data into file.sql file?

Question

I'm using SQL Server[s] 2008 R2 [Express].

I would like to create an installation file.sql file for an example database.

In file.sql I mean a file that will run from Microsoft SQL Server Management Studio as a query.

I would like to have a single file including the database itself and all tables and data.

How do I export an entire database into such a file?

P.S.

It is a very small database.

I do not worry about database name duplicate on the new server.

There are Unicode characters and many special characters in data including {[(<`.,'|"?*&/>)]}

Answer 1

In management studio

Right click on Database name

choose Tasks > Generate scripts

Next, Choose "Script entire database"

Next, go to Advanced

Types of data to script => choose "Schema and Data" (and check other options for your needs)

Select file to save to

Finish.

EDIT :

If it's used in Management Studio only, you'll be faster using Backup and Recovery !

Answer 2

I would just add the caveat that if you have large amounts of data (> 1000 records) then you need to batch the insert statements into groups of <= 1000 inserts at a time. There is a limitation on batch size.

You may also need to pay attention to the order in which inserts occur to avoid constraint conflicts. A safe approach would be to extract the FK constraints into a separate file which is executed last.