How to dump a Microsoft SQL Server database to a SQL script?

227,000

Solution 1

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.

Solution 2

Try Sql Server Database Publishing Wizard. You may need to re-order the script to make it run in one shot though because of dependencies but it will include your schema and data.

If you havent got 2005 SQL XMO objects installed, you'll get an error when you run the wizard. You'll want the Microsoft SQL Server 2005 Management Objects Collection

Solution 3

I found SQL Dumper pretty useful. It's free so you can give it a try. It lets you choose the database tables and columns, views and even the results of custom queries as SQL insert statements.

Solution 4

Not finding the right tool, I decided to create my own: a sqlserverdump command line utility. Check it out on http://sqlserverdump.codeplex.com/. It will recreate schema and data in a single step.

Solution 5

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.

Further documentation at link:

http://blog.sqlauthority.com/2011/05/07/sql-server-2008-2008-r2-create-script-to-copy-database-schema-and-all-the-objects-data-schema-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Share:
227,000

Related videos on Youtube

Matt Sheppard
Author by

Matt Sheppard

Software engineer in Canberra, Australia

Updated on June 14, 2022

Comments

  • Matt Sheppard
    Matt Sheppard less than a minute

    Is there any way to export a Microsoft SQL Server database to an sql script?

    I'm looking for something which behaves similarly to mysqldump, taking a database name, and producing a single script which will recreate all the tables, stored procedures, reinsert all the data etc.

    I've seen http://vyaskn.tripod.com/code.htm#inserts, but I ideally want something to recreate everything (not just the data) which works in a single step to produce the final script.

    • Julio César
      Julio César almost 14 years
      @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.
    • user24161
      user24161 over 12 years
      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.
    • kenorb
      kenorb over 4 years
    • 030
      030 over 3 years
      @MattSheppard please consider to accept one of the answers
  • Daniel Silveira
    Daniel Silveira about 13 years
    Actually, you don't need to reorder the script, because it drops all constraints, create the schema, insert the data, and, finaly, recreate the constraints.
  • Beep beep
    Beep beep almost 12 years
    Awesome, that's pretty neat
  • jscott
    jscott about 10 years
  • VMAtm
    VMAtm almost 10 years
    Last stable version doesn't work with stored procedures.
  • RomanSt
    RomanSt almost 10 years
    Just remember to go into advanced options and tell it to script not just the schema, but also the data.
  • solidau
    solidau over 9 years
    @romkyns deserves all the up-votes! "Types of data to script" is the exact option name. You will want to select "Schema and data."
  • David Smithers
    David Smithers almost 9 years
    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.
  • Nam G VU
    Nam G VU almost 7 years
    This is great tool though only support SQL Server 2015. How about 2008 and later?
  • Raynet
    Raynet over 6 years
    Last version can be downloaded from download.cnet.com/SQL-Dumper/3000-10254_4-10514574.html
  • TheLegendaryCopyCoder
    TheLegendaryCopyCoder 3 months
    Its no longer available. Do perhaps have a copy you can host in GitHub?