1

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

1
  • Try my suggestion to your query. Edit:1 Jul 29, 2019 at 10:35

2 Answers 2

1

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!

1

The answer by Rajesh will definitely work for you. But I literally just had to do this for a slew of databases that we were migrating to Azure. For our Azure SQL DB targets, we decided to do BCP for the extract and load. I developed this powershell for me to generate the commands.

It also includes a lot of lessons learned:

  • Really, always use a format file with custom delimiters for tab and new line (this one does that).
  • For insert, make sure to use -q, -b and -h TABLOCK or you will have problems.
  • This script generates a batch file with all of the commands in them, then executes that (or you can elect to just have it generate the commands and then you run them later. It generates both the export and the import comamnds.
  • The export command is fairly simple with no variables.
  • The import command is far more dynamic with variables at the top. It also assumes that your target is a server that requires a username/password.
  • If in Azure, make sure that the database target size is appropriate for the source data. I had one case where it loaded a bunch then hit the max size for the database (default to 32GB) and barfed.
  • The export will also compress the entire folder when it's done.

My workflow for this was to generate the commands ahead of time, then on day of cut, run the export, copy zip to cloud, unzip, edit the Import command, then run it.

Function Export-BCP ([string]$ServerInstance, [string]$DatabaseName, [string[]]$TableName = @(), [string[]]$SchemaName = @(), [string]$ExportPath, [string]$PathToBCP, [boolean]$WaitForComplete = $false)
{

    Write-Debug @"
/** Export-BCP

    ServerInstance:`t$($ServerInstance)
    DatabaseName:`t$($DatabaseName)
    SchemaName: `t$($SchemaName | Format-List | Out-string)
        - If SchemaName is empty then *all* schemas will be extracted
    TableName:`t$($TableName | Format-List | Out-string)
        - if TableName is empty then *all* tables
    ExportPath:`t$($ExportPath)
    PathToBCP:`t$($PathToBCP)
    WaitForComplete:`t$($WaitForComplete)
    **/
"@

    $CompressionDestination = [system.io.path]::combine($ExportPath, "$($DatabaseName).zip")
    $ExportPath = [system.io.path]::combine($ExportPath, $DatabaseName)

    If((test-path -LiteralPath $ExportPath -PathType container) -eq $false)
    {
        Write-Debug "`t - Creating Export Directory"
        New-Item -Path $ExportPath -ItemType directory | Out-Null 
    }

    If((test-path -LiteralPath ([System.io.path]::Combine($PathToBCP, "bcp.exe"))  -PathType leaf) -eq $false)
    {
        Write-Error "Unable to find bcp.exe" -ErrorAction Stop 
    }

    $CommandOutput_Export = ([System.io.path]::Combine($ExportPath, "!BCPExport.bat"))
    $CommandOutput_Import = ([System.io.path]::Combine($ExportPath, "!BCPImport.bat"))

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null 
    $SMO = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerInstance

    $DB = $SMO.Databases[$DatabaseName]

    If($SchemaName.Count -eq 0)
    {
        $SchemaName = $DB.Schemas | Where-Object -FilterScript {$_.IsSystemObject -eq $false -or $_.Name -eq "dbo"} | Select-Object -ExpandProperty Name
    }

    $SchemaTableList = @()

    #Loop through schema list.
    ForEach ($Schema In $SchemaName)
    {

        Write-debug "`t Working on Schema: $($Schema)"

        If($TableName.Count -eq 0)
        {
            write-debug "TableName.Count was 0"
            $SchemaTableList += ($DB.Tables | Where-Object -FilterScript {$_.Schema -eq $Schema -and $_.IsSystemObject -eq $false} | Select-Object @{Name="FQDN";Expression={$Schema + "." + $_.Name}} | Select-Object -ExpandProperty FQDN)
        } else {
            ForEach ($T in $TableName)
            {
                Write-debug "`t Working on Table: $($T)"
                IF($T.split(".").Count -eq 1)
                {
                    Write-debug "`t Matching specific tables for any schema."
                    $SchemaTableList += ($DB.Tables | Where-Object -FilterScript {($_.Schema -eq $Schema -and $_.IsSystemObject -eq $false) -and $_.Name -ilike $T} | Select-Object @{Name="FQDN";Expression={$Schema + "." + $_.Name}} | Select-Object -ExpandProperty FQDN)
                } elseif ($T.split(".")[0] -ilike $Schema)
                {
                    Write-debug "`t Matching specific tables in specific schemas."
                    $SchemaTableList += ($DB.Tables | Where-Object -FilterScript {($_.Schema -eq $Schema -and $_.IsSystemObject -eq $false) -and $_.Name -ilike $T.split(".")[1]} | Select-Object @{Name="FQDN";Expression={$Schema + "." + $_.Name}} | Select-Object -ExpandProperty FQDN)
                }
            }
        }
    }

    If($SchemaTableList.Count -ge 1)
    {

        #Generate the Change Directory Commands 
        "cd /d `"$($PathToBCP)`"" | Out-File -LiteralPath $CommandOutput_Export -force -Encoding ascii 

#For the Import side...
        @"
REM /** SET LOCAL VARIABLES
REM     bcplocation - path to BCP.exe (wrap in quotes)
REM     filelocation - current location to the .dat and .xml files.  needs trailing \ and do NOT wrap in quotes.
REM         - should be set automatically by run-time variable.
REM     targetservername - name (including protocol and port) of the target SQL instance.  wrap in quotes.
REM     targetserveruser - name of the target user.  wrap in quotes. 
REM     targetserverpass - password to use.  wrap in quotes.
REM
REM     **/
REM 

SET bcplocation=`"$($PathToBCP)`"
SET filelocation=%~dp0
SET targetservername="tcp:servername,1433"
SET targetserveruser="user"
SET targetserverpass="password"

cd /d %bcplocation%
"@ | Out-File -LiteralPath $CommandOutput_Import -force -Encoding ascii 

        $CurrentDT = [string](Get-Date -Format "yyyyMMdd_HHmmsstt")

        ForEach ($Ex in $SchemaTableList)
        {
            $EX_ObjectNameFQDN = "[$($DatabaseName)].[$($Ex.Split(".")[0])].[$($Ex.Split(".")[1])]"
            $EX_FileName = [System.io.path]::Combine($ExportPath, "$($EX_ObjectNameFQDN).dat")
            $EX_FormatFileName = [System.io.path]::Combine($ExportPath, "$($EX_ObjectNameFQDN).xml")
            Write-Debug "`t - $($EX_ObjectNameFQDN)"

            #Generate the OUT commands.     
            #https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017
            #out, -S <Server> -T <trusted> -t<delimiter> -c <use character types>
            #custom field and row terminators in the output format file.
            "BCP.EXE $EX_ObjectNameFQDN format nul -c -x -t `"<@@%FT%@@>`" -r `"<##NL##>`" -f `"$($EX_FormatFileName)`" -T -S $($ServerInstance)" | Out-File -LiteralPath $CommandOutput_Export -Append  -Encoding ascii 
            "BCP.EXE $EX_ObjectNameFQDN out `"$($EX_FileName)`" -T -S $($ServerInstance) -f `"$($EX_FormatFileName)`"" | Out-File -LiteralPath $CommandOutput_Export -Append  -Encoding ascii           

            #Generate the IN commands.
            #https://blog.atwork.at/post/2017/12/07/Import-and-export-to-an-Azure-SQL-Database
            "BCP.EXE $($EX_ObjectNameFQDN.Replace('[', '').replace(']', '')) in `"%filelocation%$($EX_ObjectNameFQDN).dat`" -q -h `"TABLOCK`" -E -b 10000 -f `"%filelocation%$($EX_ObjectNameFQDN).xml`" -S %targetservername% -U %targetserveruser% -P %targetserverpass%"

        }

        #Add compression to last step.
        "REM " | Out-File -LiteralPath $CommandOutput_Export -Append  -Encoding ascii 
        "REM Compressing Directory" | Out-File -LiteralPath $CommandOutput_Export -Append  -Encoding ascii 
        "powershell.exe -nologo -noprofile -command `"& {Add-Type -A 'System.IO.Compression.FileSystem'; [IO.Compression.ZipFile]::CreateFromDirectory('$($ExportPath)', '$($CompressionDestination)');}`"" | Out-File -LiteralPath $CommandOutput_Export -Append  -Encoding ascii 

    } else {
        Write-Error "No Objects Found To Export" -ErrorAction Stop 
    }

    IF($WaitForComplete -eq $false)
    {
        write-debug "Staring Export - Asynchronously"
        Start-Process -FilePath $CommandOutput_Export
    } else {
        Write-debug "Starting Export - Waiting for completion"
        Start-Process -FilePath $CommandOutput_Export -Wait 
    }


}

Set-Variable DebugPreference -Value Continue

$SourceSQL = "<sourceservername>"
$BCPPath = "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
$ExportPath = "C:\temp\BCPExport"

#Each database will have a folder created for it.
$Database = "databasename"

#All Tables
Export-BCP -ServerInstance $SourceSQL -DatabaseName $Database -ExportPath $ExportPath -PathToBCP $BCPPath -WaitForComplete $false

#Specific tables....
Export-BCP -ServerInstance $SourceSQL -DatabaseName $Database -TableName @("dbo.table1","dbo.table2")  -ExportPath $ExportPath -PathToBCP $BCPPath -WaitForComplete $false

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.