Export entire database to Flat file

Export entire database to Flat file

Post by James Hoke » Sat, 10 Jan 2004 19:26:28



RR,

There is no direct way to do this.

Even if you could, it's likely that nobody would be able to use it.

What is your goal here? Maybe there are other ways to achieve it.

James Hokes


Quote:> Hi Guys,

> How do you export an entire database into a flat file in SQL server? Is it

possible?
 
 
 

Export entire database to Flat file

Post by Keith Kratochvi » Sat, 10 Jan 2004 19:44:33


You could BCP the data out of each table.  What do you want to do with this flat file?

--
Keith


> Hi Guys,

> How do you export an entire database into a flat file in SQL server? Is it possible?


 
 
 

Export entire database to Flat file

Post by Aaron Bertrand - MV » Sat, 10 Jan 2004 19:51:59


Not sure why you'd want to do that, or what use it would have.  What I would
do is detach the database, copy the MDF/LDF files, and re-attach the
database.  Or perform a backup.  Then you can just attach the database from
that point in time to any other system, rather than have to rebuild from
whatever you think a "flat file" will look like.  One of the concepts many
people miss about SQL Server is that it's not a simple file-based database
like Microsoft Access.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


Quote:> Hi Guys,

> How do you export an entire database into a flat file in SQL server? Is it

possible?
 
 
 

Export entire database to Flat file

Post by lindawi » Sat, 10 Jan 2004 21:39:12


RR,

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
applications.

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
current favorite:

http://www.ispirer.com/products/

Linda

P.S. to the guys who don't understand why anyone would want to do
this.

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
installation process.

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.

 
 
 

Export entire database to Flat file

Post by Aaron Bertrand - MV » Sat, 10 Jan 2004 21:45:43


Quote:> 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.

I've found that they compress quite well, if they are in fact relatively
empty.

However, point taken about exporting data for import into other systems.

Quote:> 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.

I don't see where anyone in this thread was being unhelpful.  Getting
further information about *why* they wanted to do it might yield a better
answer (e.g. to a question the original poster never thought to ask).  I
don't think it's automatically evil when a poster's intent is questioned.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 
 
 

Export entire database to Flat file

Post by lindawi » Sat, 10 Jan 2004 21:39:12


RR,

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
applications.

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
current favorite:

http://www.ispirer.com/products/

Linda

P.S. to the guys who don't understand why anyone would want to do
this.

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
installation process.

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.

 
 
 

Export entire database to Flat file

Post by Keith Kratochvi » Sat, 10 Jan 2004 22:48:00


You have many options.  Some that come to mind:

Are the servers on the same network?  Perhaps you could set up a linked server and insert the data into the other DBMS.

Perhaps you could use something like Largo SQL Tools (http://www.largosqltools.com/) , ObjectScripter(http://www.rac4sql.net/), QALite (http://www.rac4sql.net/), Lockwood Tech SQL Inserts (http://www.lockwoodtech.com/)  to create the insert statements for you.

Another option would be to export the data within each table to a text file via a tool such as BCP.  You could then import the data into the other system.

--
Keith


> I want to import the flat file to another DBMS

 
 
 

Export entire database to Flat file

Post by James Hoke » Sun, 11 Jan 2004 02:01:30


lindawie,


Quote:> P.S. to the guys who don't understand why anyone would want to do
> this.

> 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.

LOL. Thanks for the DBA lesson.

What we were questioning was the apparent request to have it all
in _one_ flat file, which did not make sense.

Quote:> 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.

No, there are "file crackers" for just exactly this purpose.
Just tell it you want 650MB or 4.7GB, whichever.
There is also always tape.

Quote:> 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.

Yes, as I said before, he asked how the get the _database_ into a flat file,
which is a vastly different question than saying,  "how do I export my table
to a flat file?"

There are tons of newbies in here; we cannot assume that the poster knows
full well what he/she is talking about. Therefore, clarification is
requested.
For all we knew, this person actually thought you could get a db into a flat
file,
which, of course, you cannot.

James Hokes