Sql-server – Import table data into a text file

powershellsql serversql-server-2016

I'm new to powershell. I need to write a script which will import data for each table into separate text file.For sample table, I tried with below script:

    bcp -u sa por.dbo.audit out D:\tbackup\audit.dat

and I received below error:

Copy direction must be either 'in', 'out' or 'format'.
usage: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]

Might be it's a silly question. But, any immediate help will be appreciated

Best Answer

If you are using Windows Authentication to connect you sql server, use following command.

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -t -n

If using SQL Server authentication, use this one

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -U Login -P password -t -n

Edit:1

The error appears because, giving variable as an input to 'bcp` accepted as a value. So if I use

bcp $Database+'.'+$Schema+'.'+$Table out $BCP_To+$Table+$ExporttoFile -S $Server -T -t -n

here $Database+'.'+$Schema+'.'+$Table assumed as single value Database.dbo.TableName (a table) and $BCP_To+$Table+$ExporttoFile is another value. (not taking as path).

So, instead of using above command in powershell. I prepared command to a variable and executed it using Invoke-Expression. You can say it dynamic powershell command execution.

Here is complete power-shell command to export you all tables of a database into .txt file.

[reflection.assembly]::loadwithpartialname('Microsoft.SQLServer.SMO') | out-null

$Server = 'SQLServer'
$Database = "Database"
$BCP_To = "D:\tbackup\"
$ExporttoFile =".txt"

[string]$Table
[string]$Schema

$Server = New-Object('Microsoft.SQLServer.Management.SMO.Server') $Server

foreach ($Tables in $Server.Databases[$Database].Tables | SELECT Schema,Name)
{
$Schema = $Tables.Schema
$Table = $Tables.Name

$BCP = 'bcp "'+$Database+'.'+$Schema+'.'+$Table+'" out "'+$BCP_To+$Table+$ExporttoFile+'" -S '+$Server+' -T -t -n'

$BCP = $BCP.Replace('[','')

$BCP = $BCP.Replace(']','')

Invoke-Expression $BCP
}

Hope it'll help you.

Thanks!

Related Question