question

postonoh avatar image
postonoh 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?
sql-server-2008
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What do you want to do with the file?
0 Likes 0 ·
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?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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`?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, sounds like a backup to me!
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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. :-)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks I will try.
0 Likes 0 ·
+1 This is the route I would go if I had to script out to a single file. The only reason I could see to want to do such a thing would be to possible script out a small database from one version to another. However there are countless ways to accomplish that.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.