INF: How to Bulk Copy Out All the Tables in a Database

ID: Q176818

The information in this article applies to:

SUMMARY

This article provides a script that uses the bulk copy program (bcp) to bulk copy all your user-defined tables on any given database. This script was designed to accommodate both integrated and standard security. You can also choose to specify either native or character mode bcp. The procedure in this article is coded to use character mode, but you can simply change one line to set the mode to native, if you want.


MORE INFORMATION

The script below dynamically codes a SELECT statement, using the parameters supplied to it, and builds a batch file containing the bcp instructions to get the data from your tables. You need to run this batch file, which will actually execute the bcp commands. Also note that you may not get the expected results if you are using extended characters. For more information on extended characters, see the following article in the Microsoft Knowledge Base:

Q153449 : INF: SQL Server Code Pages and AutoAnsiToOem Behavior

After you compile this stored procedure, you can run it from the command prompt with the following command (note that the command should all be typed on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following table describes the parameters in the above command: 

   Parameter                              Description
   ------------------------------------------------------------------------
   isql -E                                Starts a trusted connection in 
                                          ISQL.

   /dpubs                                 The database name where you want
                                          to store this procedure.

   -Q                                     Executes the query and
                                         immediately exits ISQL.

   "sp_bcpTablesOut 'pubs', 'c:\bcp\' "   This is the command to be
                                          executed by ISQL. *

   -oc:\bcp\bcpscript.bat                 The batch file generated by the
                                          stored procedure. **

   /h-1                                   Suppresses headings. ***

   /n                                     Suppresses the prompt (>) and
                                          numbering. ***

NOTES: 

   *   The stored procedure in this example (sp_bcpTablesOut) is passed two
       parameters: the database that you want to bulk copy the tables out
       to ('pubs'), and the directory where you want to place the tables
       that will be bulk copied out to ('c:\bcp\'). Also note that there is
       a backslash after the subdirectory name. You could also pass two
       other parameters to specify LOGINID and PASSWORD.

   **  You have to run the Bcpscript.bat script to actually bulk copy the
       tables. To capture the results of this script into a file, run it
       with the following command:

          c:\bcp\bcpscript.bat >c:\bcp\results.txt

   *** By default, ISQL gives you headings for each result set. Because you
       are creating a script, you want to suppress the headings because
       they cannot be executed. ISQL also gives you the prompt (>) and
       numbers the lines. Again, you are creating a script, so you only
       want code that can be executed.

The following is an example of how to run this procedure in integrated mode (note that the command should all be typed on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following is an example of how to run this procedure in standard mode (note that the command should all be typed on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\', 'SA' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following is an example of how to run the batch file and pipe results to a text file:
c:\bcp\bcpscript.bat >c:\bcp\results.txt


Source of the Stored Procedure

The following is the source of the stored procedure. Copy and paste it in your database, and then compile it: 

   if exists (select * from sysobjects where id =
   object_id('dbo.sp_bcpTablesOut') and
          sysstat & 0xf = 4) drop procedure dbo.sp_bcpTablesOut
   GO

   /*    dbname = database name
      dirname = destination directory for bcp output
      username = optional
      pwd = optional
   */ 

   CREATE PROCEDURE sp_bcpTablesOut  @dbname  varchar(40),
                               @dirname varchar(20),
                          @username varchar(30) = NULL,
                           @pwd varchar(30) = NULL
                                        AS

   set nocount on -- removes the rows affected count

   /*   @Q1       represents single quote
        @Q2       represents double quotes
      @security       represents security for BCP
      @myquery    represents file bcp batch output
   */ 

   declare @Q1 char(1)
   declare @Q2 char(1)
   declare @security varchar(255)
   declare @myquery varchar(255)
   declare @bcpmode char(3)

   select @Q1 = "'"
   select @Q2 = '"'
   select @bcpmode = '-c '    -- character type
   --select @bcpmode = '-n '  -- native type

   /*    checks for standard or integrated security  */ 

   IF @username IS NULL
          select @security = '-T '
   ELSE
          select @security = '-U' + @username + ' -P' + @pwd

   /*    formats final bcp output text that will be part of the script   */ 

   select @myquery = 'SELECT ' + @Q2 + 'bcp ' + @dbname + '..' + @Q2 + ' +
   name + ' + @Q2 +
      " out " + @dirname + @Q2 + ' + name + ' + @Q2 + '.txt '+ @bcpmode +
   @security +
           @Q2 + ' from ' + @dbname + '..sysobjects where type = ' + @Q2 +
   'U' + @Q2 +
      ' order by name'

   execute(@myquery)
   GO

It is very important that you validate the row count returned by the execution of bcp. To retrieve a row count of every table in the database, see the following article in the Microsoft Knowledge Base:
Q176426 : INF: How to Determine Number of Rows of Every Table in Database

The following is an example of the batch file created by this procedure (Bcpscript.bat): 

   bcp pubs..authors out c:\bcp\authors.txt -c -Usa -P
   bcp pubs..discounts out c:\bcp\discounts.txt -c -Usa -P
   ...

The following is an example of the bcp results, piped into a Results.txt file: 

   C:\>bcp pubs..authors out c:\bcp\authors.txt -c -Usa -P
   Starting copy...
   23 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.): total =     31 Avg =      1 (741.94 rows per sec.)

   C:\>bcp pubs..discounts out c:\bcp\discounts.txt -c -Usa -P
   Starting copy...
   3 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.): total =      1 Avg =      0 (3000.00 rows per sec.)
   ...

Additional query words: records export download dump 


Keywords          : kbcode SSrvGen 
Version           : WINNT:6.0 6.5
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: April 13, 1999