|
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. | |||
|
feedback
|
This question came from our site for professional and enthusiast programmers.
This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.
|
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 | |||||||
feedback
|
|
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 foreing keys. If you combine it with that script for the inserts you'll get pretty much what mysqldump produces. TIP: In the final step select "Script to a New Query Window", it'll work much faster that way. | |||
|
feedback
|
|
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. | |||
|
feedback
|
|
Try DBSourceTools. It's designed to script out a source database, and re-deploy to a target database. It scripts schema and data. | |||
|
feedback
|
|
@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. | |||||
feedback
|
|
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. | |||
|
feedback
|
|
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/ | |||
|
feedback
|
|
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. | |||
|
feedback
|
|
I invoked that wizard (SQL Server 2005) and the script declares all the stored procedures before any of the tables they reference. Still, I'm glad you pointed it out, because I'm only trying to find differences in two databases that are supposed to be the same in structure. I can drag the files over to Unix and run diff or sdiff. | ||||
|
feedback
|
