This works in SQL 2008 R2 and higher (so far has worked in express and standard versions)

Ever have a Microsoft SQL database in development that you have locked down and you want to move to production, or in my case, you have a new development environment that you want to move to.  I will show you how to export your database to a simple SQL script that can be executed anywhere.

Note: the Screenshots are based off the SQL Studio Management Studio 2012 but the 2008 R2 is very similar, just ask if you cannot find the options.

• First thing to do is right click that database
• Select Task
• Select Generate Scripts

• From the introduction screen click Next
• Select “Script entire database and all database objects”
• Click Next
• From the Set Scripting Options, click on “Advanced”.
• Under general look for “Types of data to script”

• These options are pretty self explanatory but for those that are just learning
  • Data Only – This generates the insert statements for the data contained in the tables.  Example of where this is useful, Entity Framework code first, rebuild the database off the model, this script can be run to load the data back into it.
  • Schema Only – This generates the database structure only.  This includes Index’s, primary, Foreign keys and so on.  Example of where this is useful, moving a database from development to production or an application that shares the same structure but the data is different between environments.
  • Schema and data – This is a script of both
• For this example select ‘Schema and data’
• Click OK
• Click Next
• Review the selections and click next again
• When complete you will get a report about the generated scripts. any errors will be listed here

• You are now done, take your script to the other database and run it.