Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
This is a duplicate of serverfault.com/questions/147638/… –  DaveG Sep 9 at 11:18

6 Answers 6

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

share|improve this answer

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

share|improve this answer
    
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.

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

You can always go with PowerSQL dump tool. Does the job every time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.