I have a need to dump a fairly significant number of Microsoft SQL Server databases to .sql files. I have a Unix background, and am used to using tools like mysqldump and pg_dump for MySQL and PostgreSQL respectively.

Is there an equivalent program that will do this. I'm well aware of the regular backup utilities - and I can't use them because I wish to do content search/replace on the SQL itself when importing these databases onto new servers. Thanks!


Have you considered using SSIS yet?

SSIS does the transofrmation of the data BEFORE loading it into the target system, hence avoiding a lot of read/write on the target system.

You can generate a SSIS package via sript or .NET code also.

Get started here: http://msdn.microsoft.com/en-us/library/ms141026.aspx


[note, this is a duplicate that I can't seem to find right now...]

In SQL Server Management Studio

  1. Right-click on the database
  2. Choose Tasks -> Export Data...
  3. Finish off the wizard according to your wishes.

For SQL Server 2005+

  • Thanks for the response. I'm looking for a more programmatic method. I have a few thousand customer databases to do ;) Incidentally, Under the "Tasks" context menu I see "Detach...", "Shrink", "Back Up...", "Restore" and "Generate Scripts". There is no "Export Data..." option. (This is SQL Server Management Studio Express). I think something like BCP is what I need, but it doesn't create SQL statements.
    – Duel
    Aug 27 '09 at 19:38
  • Ah, right. Yeah, using the wizard for that would truly suck. Not sure about the option in SSMS Express, I don't have it handy.
    – squillman
    Aug 27 '09 at 19:43

I think you are looking for SMO, Sql Management Objects.



There's no single utility to automatically script out the tables and all the data.

The typical solution that I'd use for something like this would be to write a SQL Script to handle the data change, and then backup the databases from the old server, and restore them to the new server, then run the SQL Script.

It's probably not the easiest solution, but this way you are sure to be migrating the newest data.


You should take a look at the SQL Database Publishing wizard which is an extra download add-in for visual studio. That will do you export to .sql although I don't know if it provides command line options so that you could script it for all databases quickly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy