SQL Server 2008 R2 – Export Database to Platform Independent Data Files

exportsql-server-2008-r2

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.

Best Answer

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.

Related Question