I have used mysqldump on a mysql database lots of times and am familiar with how it works. Does anyone know if there is a similar tool that could be used on a sql server database.
I know sql server can backup its database but I would like to get all the sql insert statements out into a text file if possible.
There is a freeware which creates SQL insert statements from the data of your tables:
[SQL Dumper] (Moderator note: link removed due to malware-infested site)
SQL Server Dumper enables you to dump selected SQL Server database tables into SQL INSERT statements, that are saved as local .sql files and contain all the data required to create a duplicate table, or to be used for backup purposes. You can choose to create an individual .sql file for each table, or combine all selected tables into a single file.
This program implements the following special features:
- Foreign Keys order tables in text file in order to insert data without colisions/errors
- Primary Key IDENTITY guarantees the value of the IDENTITY field
- Reference to itself eliminates the constraint and at the end create it again
Use it in combination with SQL Server's built-in schema generation (object scripting).
aside from writing your own sql to do this, there are commercially available tools eg:
For Sql Server 2005 and 2000, Microsoft has the Database Publishing Wizard which you can download here for free. A newer version of that tool is built into Sql Server 2008.
SQL Server 2008 has a new option that will allow you to do this. There is a decent article that explains how to do this. Earlier versions of SQL Server do not have this functionality built in and would require an external tool to generate the script for you.
Had a quick look at mysqldump (as I'm a SQL Server expert) and there's nothing native in SQL Server before 2008 that will do the same thing. However, you can script out the table definitions from the database (using SQL Server Management Studio) and then you can export all the table data into a file, in native (i.e. raw) or readable format.
That will allow you to recreate the database and insert the data in the way you want - using the SQL Server import tools, or you can create the insert statements yourself (although you'll start getting trickiness with identity values etc).
I wouldn't recommend doing it yourself though - if you really want this method, try one of the (non-free) tools available. The only one off the top of my head is Apex SQL Script.
On 2008 you can do this using SQL Server Management Studio without any of the nastiness of building scripts or buying another product.
Hope this is helpful!
