4

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!

  • This is a duplicate of http://serverfault.com/questions/147638/dump-microsoft-sql-server-database-to-an-sql-script?rq=1 – DaveG Sep 09 '14 at 11:18
  • 1
    Possible duplicate of [Dump Microsoft SQL Server database to an SQL script](https://serverfault.com/questions/147638/dump-microsoft-sql-server-database-to-an-sql-script) – kenorb Feb 08 '18 at 16:01

5 Answers5

1

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

Michael Niemand
  • 169
  • 3
  • 17
0

[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+

squillman
  • 37,618
  • 10
  • 90
  • 145
  • 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. –  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
0

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

http://msdn.microsoft.com/en-us/library/ms162169.aspx

Mark
  • 2,248
  • 12
  • 15
0

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
0

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.

Chris W
  • 2,670
  • 1
  • 23
  • 32