How to export data from SQL Server 2008.2010 in DML (SQL script)?

sql, sql-server, sql-server-2008

Solution

Here are the steps to generate the scripts from a database along with data. The screenshots were taken using `SQL Server 2012 Management Studio` but I believe that the steps are similar for SQL Server 2008 as well.

- Connect to the instance in which you would like to scrip the database along with data.

- Right-click on the database and select the option `Tasks --> Generate Scripts...`

- On the Generate and Publish Scripts wizard, click `Next` button.

- On the Choose objects step, I have left the default selection `Script entire database and all database objects` checked. You can also select the option `Select specific database objects` and pick the objects of your choice.

- On the Set Scripting Options step, select the path where you would like to save the script. Click on the `Advanced` button.

- On the Advanced Scripting Options dialog, scroll down to the option `Types of data to script`. By default, this is set to `Schema only`, which means it will generate only the create object statements. If you want both object creation script along with data, select the option `Schema and data`. Click OK on the Advanced Scripting Options dialog and click Next on the Set Scripting Options step

- On the Summary step, it will show all the options that you had selected. Click Next.

- On the Save or Publich Scripts step, if everything goes well you will see Success status appear against each object and the script should be saved to the location that you specified on the Set Scripting Options step.

Scripting the entire AdventureWorks database turned to be a huge `522 MB` script file!

Hope that helps.

Problem

Original source