Microsoft KB Archive/176818

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

Article ID: 176818

Article Last Modified on 2/20/2004

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q176818



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:

153449 : 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:

176426 : 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: kbhowto kbcode KB176818

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.