This question came from our site for professional and enthusiast programmers.

@Matt Yes, it doesn't export the data. That's why I mentioned that you should combine it with the script you suggested. This method does builds a script in the correct order though.
–
Julio CésarAug 25 '08 at 2:40

Actually, in Mangement Studio 2008, you just have to turn on the "export data" option, and the script will contain both schema and insert statements.
–
user24161Feb 10 '10 at 21:37

8 Answers
8

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.

Just remember to go into advanced options and tell it to script not just the schema, but also the data.
–
romkynsAug 22 '12 at 14:50

1

@romkyns deserves all the up-votes! "Types of data to script" is the exact option name. You will want to select "Schema and data."
–
Juventus18Jan 16 '13 at 20:17

1

Main issue with scripts generated from SSMS is that they are not ordered correctly to take dependencies into account. This is not a problem for small databases where you can do this manually but it is definitely a problem when database gets over 50 objects. So far we successfully used ApexSQL Script for this. It’s a premium tool but you can use it in trial mode to get the job done. I think Red Gate also has a similar tool.
–
David SmithersJun 21 '13 at 8:12

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.