Export a Database to SQL Script for SQL 2008R2/2012

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

CropperCapture[1]

  • 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”

CropperCapture[2]

  • 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

CropperCapture[3]

  • You are now done, take your script to the other database and run it.
Advertisements
This entry was posted in Database and tagged , . Bookmark the permalink.

One Response to Export a Database to SQL Script for SQL 2008R2/2012

  1. Ryan says:

    Many thanks. For 2012, there is a lack of general advice on how to export with data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s