2

On a quarterly basis I need to provide our clients a copy of all their data. We need to provide this data in a database independent format (in other words I cannot simply send them a backup file). The typical client has roughly 1GB of data. This includes BLOB fields with files up to 10mb in size. These files are a mix of Office documents, PDFs, and images.

I’ve looked into both the BCP and the XML query capabilities, and from my understanding both only seem to work at the table level which would mean I would have to write a query per table. The schema size of this database makes those options prohibitively time consuming, not to mention future maintenance of this process seems costly.

The database independent format can be anything. XML and scripts (scripts have to support both Oracle and SQL) are less desirable due to the storage size that these typically require.

  • If you have 20 tables or 2,000 tables it really shouldn't take you a different amount of time to build select statements using dynamic SQL. – Aaron Bertrand Feb 23 '15 at 18:30
2

This Powershell script should be able to solve most of your issue. I use a system table select to get my list of tables, then BCP to export all the data. The challenge is exporting your BLOBs. This script will export those as character data, which may not be useful to you in your situation.

Import-Module sqlps -DisableNameChecking
$outputdir="C:\TEMP"
$database="AdventureWorks2012"

$tables = Invoke-SqlCmd -ServerInstance localhost -Database $database -Query "SELECT s.name+'.'+t.name TableName FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE t.type = 'U'"

foreach($table in $tables.TableName){
    $outfile = Join-Path -Path $outputdir -ChildPath "$table-$(Get-Date -Format 'yyyyMMddhhmm').csv"
    $cmd = "& bcp $database.$table out '$outfile' -T -k -c -t','"
    Invoke-Expression $cmd | Out-Null
}

For an Oracle export, you'll want to use the import/export tool, but the process is more or less the same.

  • In regards to the BLOB fields, can you specify the Unicode encoding with BCP? Or do you know what it will use by default? If we provide the encoding with the data the client should be able to reconstruct the files. Thanks again – That Bryan Davies Feb 24 '15 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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