magritton
6/4/2014 - 11:50 AM

Extracts data from a database using an SQL file and powershell. Designed to be used on the SQL server and outputs as a CSV. See this link: h

Extracts data from a database using an SQL file and powershell. Designed to be used on the SQL server and outputs as a CSV. See this link: http://www.sqlservercentral.com/articles/Extracting+data/109666/

PS Extract data from Database.ps1
content_copyfile_download
$ErrorActionPreference="Stop"

if ($args.Count -le 2)
{
    throw "Usage: <server name> <db name> <script file>"
}

$servername = $args[0]
$database = $args[1]
$sourcefile = $args[2]

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)
$1 = Get-Item($sourcefile)
$ds = $server.Databases[$database].ExecuteWithResults("$(Echo $1.OpenText().ReadToEnd())")

$cnt = 0

Foreach ($dt in $ds.Tables)
{
    $cnt ++
    Write-Host "Writing table: "$cnt
    $file = ".\" + $cnt.ToString() + ".csv"
    $dt | export-csv $file -notypeinformation
    Write-Host "Completed!"

}