Quote:> How do you export an entire database into a flat file in SQL
> server? Is it possible?
I'm not sure what you mean here...
You can back up the entire database to a file using either
Enterprise Manager or the BACKUP command in Query Analyzer. However,
the file is in a proprietary format that cannot be used by other
If you mean export to plain old text files, that's easy to do.
Export the data in each table using the bcp command line utility.
You will end up with one file per table.
Script the table structures, stored procedures, triggers, views,
using Enterprise Manager. You can either script to a single text
file or individual files for each object.
Or you can use a third party tool to do this for you. This is my
P.S. to the guys who don't understand why anyone would want to do
This is a pretty common exercise in shops that develop applicatons
that need to run on several differet platforms. I move data back and
forth between Oracle and SQL Server all the time.
If you ever need to copy even a modest sized database, 10 GB for
example, to a remote server halfway around that world, it can be
quite a bit faster to copy or ftp many smaller text files in
parallel and then load them on the other end, than to copy a single
backup file that takes hours to complete, if it doesn't fail midway
through the operation. This is how snapshot replication works. It
scripts out the tables and uses bcp to export the data.
I wouldn't advise detaching and copying .mdf and .ldf files at all,
because unless your database is already full to the brim, you'll
just waste bandwidth moving a lot of empty space around.
And don't forget copying to CD or DVD. One large backup file may not
fit on the disk, so you need to break it up into smaller files.
Exporting and scripting out everything is also one way to test your
There are plenty of good reasons for wanting to do this. The poster
is not obliged to explain himself in order to get a helpful answer.