asked
Database dump
Is there a way to dump data from a database without exporting each table into a file. I want dump all data into one file. Is this possible?
Unless you're talking about a backup I wouldn't suggest entertaining this as a data transfer method for anything over 1 GB (and that's a stretch). Can you provide some background as to what you are looking to do?
You can take a backup of a database into one file, and this will contain all the data, but in a format that only SQL Server (and other 3rd party, specific tools) can read, but I'm guessing you don't want that? Are you looking to dump the data almost like a `bcp out`?
One option could be to use the Generate Scripts Task (Right click on the database, select Tasks/Generate Scripts... Then Select All tables, and click the Advanced button. In the row "Types of data to script" select "Data Only" or "Schema And Data" if you want also to script table definitions. Then select where you want the script to be saved (file, clipboard, new script window) and SSMS will generate a single script with INSERT Statements for all tables you have selected. :-)
Well, one question is why are you trying to do this? One way to do it might be to generate the script to create all the objects using SSMS, then use SSIS to export all data to CSV files. That gives you a series of files, but it is easy to concatenate them all together, and takes only a little more work to turn the CSV files into insert statements and you can then concatenate everything together (although if you are tring to write a single script to recreate the entire database, you would need to be careful about putting everything in the right order for dependencies' sake). Of course, when you have this create script and the CSV files with all the data you could always just zip or tar them together to get it into one file. Another option is to use SSIS to send all the tables out to Access. This of course comes with limitations on size and may not preserve all the relationships and constraints, but as long as your database falls within the constraints so it could be an Access database, this would get it all into one portable file. We may be able to provide more tailored solutions if you explain why you are trying to get the entire contents of the database into one file.
You could look at 3rd party tools. [Red Gate SQL Data Compare][1] can generate a single script for inserting all data in a database, although I wouldn't do that for a database that was very big. [1]:
http://www.red-gate.com/products/sql-development/sql-data-compare/
