INF: How to Bulk Copy Out All the Tables in a Database
ID: Q176818
|
The information in this article applies to:
-
Microsoft SQL Server versions 6.0, 6.5
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