mysqldump equivalent for SQL Server

Question

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.

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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/.

Answer 6

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

Answer 7

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

Answer 8

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.