147

Is there any way to export a Microsoft SQL Server database to a 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 and 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.

4
  • @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. Aug 25, 2008 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.
    – user24161
    Feb 10, 2010 at 21:37
  • @MattSheppard please consider to accept one of the answers
    – 030
    Feb 12, 2019 at 8:34

9 Answers 9

137

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 entitled "Set Scripting Options", click on the button (on the right of the window) labelled "Advanced" and modify the option "Types of data to script" and choose "Schema and data".

TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.

4
  • 23
    Just remember to go into advanced options and tell it to script not just the schema, but also the data.
    – RomanSt
    Aug 22, 2012 at 14:50
  • 3
    @romkyns deserves all the up-votes! "Types of data to script" is the exact option name. You will want to select "Schema and data."
    – solidau
    Jan 16, 2013 at 20:17
  • 2
    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. Jun 21, 2013 at 8:12
  • This dumps as a T-SQL file. Is there an option to output as non-propriety plain old SQL?
    – otocan
    Feb 6 at 15:00
34

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

3
  • 5
    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. May 8, 2009 at 21:31
  • Awesome, that's pretty neat
    – Beep beep
    Sep 8, 2010 at 4:16
  • This is great tool though only support SQL Server 2015. How about 2008 and later?
    – Nam G VU
    Aug 20, 2015 at 0:55
12

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.

2
8

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

2
  • Any commands to install to ubuntu cli?
    – Alwin Jose
    Jan 22, 2023 at 13:45
  • 1
    @AlwinJose I haven't tried it on Linux. I don't expect it to work on another platform than Windows because of its dependencies.
    – Daniel
    Jan 23, 2023 at 14:11
6

Try DBSourceTools. It's designed to script out a source database, and re-deploy to a target database. It scripts schema and data.

1
  • Its no longer available. Do perhaps have a copy you can host in GitHub? Mar 22, 2022 at 9:51
6

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/

1
2

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.

For newer SQL versions and operating systems, this software might be worth looking into: http://sqlbackupandftp.com/

1

Check the Microsoft SQL Server Schema Dump project (mssql-schema-dump tool at GitHub).

Usage: mssqldump -h data-source-host -u username -p password [-d path/for/files] [-c] [-s] [-a] [-b DB1[,DB2[,DB3]]]

The export supports:

  • DB: Schema, User Types, User Table Types, Triggers, Full Text Catalogues, Full Text StopLists, Stored Procedures, Functions
  • DB.Tables: Schema, Triggers, Indexes, DRI, Statistics
  • DB.Views: Schema, Triggers, Indexes, DRI, Statistics
1
  • Command 'mssqldump' not found in ubuntu cli. How to install?
    – Alwin Jose
    Jan 23, 2023 at 4:01
0

Ombelt make a nice tool for exporting MS SQL server DB's. www.ombelt.com

a lot like other DB's dump facilities.

Works for me.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .