23

Is there an equivalent schema & data export/dumping tool for SQL Server as there is for MySQL with mysqldump. Trying to relocate a legacy ASP site and I am way out of happy place with working on a windows server.

Note: The DTS export utility own seems to export data, without table defs. Using the Enterprise Manager and exporting the db gets closer with exporting the schema & data... but still misses stored procedures.

Basically looking for a one does it all solution that grabs everything I need at once.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
David
  • 17,673
  • 10
  • 68
  • 97
  • 1
    Please reconsider your choice of correct answer. The answer that refers to amScript is the one that best answers your actual question. The answer you marked as correct only offers a workaround; people who find your question by googling will not find that answer very useful. – Timwi Sep 17 '09 at 16:39

11 Answers11

24

To do this really easily with SQL Server 2008 Management Studio:

1.) Right click on the database (not the table) and select Tasks -> Generate Scripts

location of tool

2.) Click Next on the first page

3.) If you want to copy the whole database, just click next. If you want to copy specific tables, click on "Select Specific Database Objects", select the tables you want, and then click next.

4.) "Save to File" should be selected. IMPORTANT: Click the Advanced button next to "Save to File", find "Types of data to script", and change "Schema only" to "Schema and data" (if you want to create the table) or "Data only" (if you're copying data to an existing table).

adding data to the script

5.) Click through the rest and you're done! It will save as a .sql file.

dallin
  • 8,775
  • 2
  • 36
  • 41
11

The easiest way is the sql server database publishing wizard.

  • Open source
  • Free
  • Does exactly what you want
  • Developed by microsoft

It does not have all the features of mysqldump but it is close enough.

http://www.codeplex.com/sqlhost/wiki/view.aspx?title=database%20publishing%20wizard

blueberryfields
  • 45,910
  • 28
  • 89
  • 168
Erick
  • 853
  • 8
  • 17
  • How do I run this? Their download does not contain any EXE file that I could run, nor any SLN or CSPROJ file that I could open in Visual Studio. Also, I notice LOADS of comments asking for help on how to use this. The impression I get is that it's the crappest piece of junk ever! – Timwi Sep 17 '09 at 15:34
  • and it's not even close to mysqldump ! – Robert Ivanc May 18 '10 at 17:45
  • The web publishing wizard does not work for SQL Server 2008. And the other poster is right, it does not even come close to mysqldump. I'm still looking for a good utility for SQL Server that does even a portion of what mysqldump can do. – Ryan Stille Sep 23 '10 at 02:13
  • Actually it does support Sql Server 2008 just fine. And yes it is not nearly as powerful as mysqldump, but if it has all the features you need, then who cares? The link above is outdated, the current version is something like 1.4 or 1.5 – Erick Oct 13 '10 at 14:33
  • @RobertIvanc At the time, and to date, it was frustrating that there wasn't a readily available command line/scriptable exe but it was the most direct option I could find. Unfortunate but it works. – David Nov 21 '18 at 02:08
10

The easiest way to move a Database would be to use SQL Server Management Studio to Export the database to another server, or if that doesn't work, make a backup like other's had suggested and restore it elsewhere.

If you are looking for a way to dump the table structure to SQL as well as create insert scripts for the data a good free option would be to use amScript and amInsert from http://www.asql.biz/en/Download2005.aspx.

If you want a good pay version I would check out Red-Gate SQL Compare and Red-Gate SQL-Data Compare. These tools are probably overkill though and probably a bit pricey if you don't intend to use them a lot. I would think it would mostly be relegated to DBAs. You can look at the Red-Gate tools at http://www.red-gate.com/.

Alex Argo
  • 8,920
  • 12
  • 43
  • 46
  • 1
    As an answer to the questions title, the amScript/amInsert tools are exactly what I was looking for. +1 – Hafthor Sep 02 '09 at 16:43
  • With both tools, you cant filter by schema before loading the database. With amScript, 150 000 table get loaded in 30 seconds. amInsert wont behave the same way, failing to load those tables. – Guillaume Massé Nov 02 '11 at 19:25
  • 1
    amScript website is down and I can't find this util with Google. There are a couple beta stage command line alternatives for mssql on GitHub, but I haven't seen anything polished yet. – Daniel Byrne May 14 '13 at 16:09
6

Not finding the right tool, I decided to create my own: a sqlserverdump command line utility. Check it out on github.

Daniel
  • 1,034
  • 1
  • 10
  • 27
2

Even easier is to use the SMO API. It lets you do exactly like mysqldump, and even better. Here is a code example:

http://samyem.blogspot.com/2010/01/automate-sql-dumps-for-sqlserver.html

0

Two things a backup/restore won't do:

  1. Get off of a Microsoft server, which was part of the original question
  2. Help quickly find a structural difference between two DBs that are supposed to have the same structure when one of them is running slowly. Unix diff, or sdiff, ignoring white space but need a way to make input files.
WGroleau
  • 448
  • 1
  • 9
  • 26
0

If you need equivalent SQL statements like CREATE TABLE... & INSERT INTO..., then I recommend you try HeidiSQL. It's a fantastic and free utility that can access Microsoft SQL Server, MySQL and PostgreSQL. It enables you to browse and edit data, create and edit tables, views, procedures, triggers and scheduled events. Also, you can export structure and data to SQL file. http://www.heidisql.com

Go to Tools / Export database as SQL and select the schema. Check the box to create the tables and “Insert” data. That’s it.

I prefer HeidiSQL to "Microsoft SQL Server Management Studio" or phpMyAdmin... etc.

panofish
  • 7,578
  • 13
  • 55
  • 96
0

Well, Mysqldump is a series of SQL statements. You can do this with DTS, but why not just create a backup and restore it on your new machine?

If you want to do it via SQL: http://msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx

Or just right click the DB and hit Tasks -> Backup (http://msdn.microsoft.com/en-us/library/ms187510.aspx)

Matt Rogish
  • 24,435
  • 11
  • 76
  • 92
  • I guess the problem is figuring out what the new service expects as far as format... We're moving the website and its DB from a dedicated win server to a shared hosting provider and the closest I can get is via plesk. – David Sep 17 '08 at 17:39
  • 1
    1st mysqldump is a command. DTS you click. You cannot automate it. 2nd The backup command writes in the server hard drive. 3rd Clicks are difficult to automate – graffic Jan 29 '09 at 07:29
0

easiest would be a backup and restore or detach and attach

or script out all the tables and BCP out the data then BCP in the data on the new server

or use DTS/SSIS to do this

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
0

SQL Enterprise manager or SQL Server Management studio have wizard based approaches, and the latter will generate the scripts so you can see how its done.

You could also use the BACKUP and RESTORE commands. More detail here: http://msdn.microsoft.com/en-us/library/ms189826.aspx

StingyJack
  • 19,041
  • 10
  • 63
  • 122
0

If you can get DTS or Integration Services to connect to both servers, you can use the wizards to 'copy objects' from one server to another. 'Copy Database' requires that the two servers can authenticate with each other, which typically means being on the same domain and that the service runs under a domain logon.

Otherwise, you can generate a script for the schema, and you can use an Integration Services/DTS package to export data to a file, then import it on the other.

We now generally use SQL Compare and SQL Data Compare. Red Gate's SQL Packager might also be an option.

Mike Dimmick
  • 9,662
  • 2
  • 23
  • 48