How to save data from all tables in SQL Server database into CSV files (using PowerShell)

  • Posted on: 23 September 2015
  • By: Michał Turecki

All it takes is to create the following PowerShell script (text file with .ps1 extension) and changing $server and $db variables to match the server's host and instance name (SERVER\INSTANCE) and a database name.

$destDir = Get-Location
$server = "localhost"
$db = "DatabaseToExport"

Import-Module -Name SQLPS
$tables = Invoke-Sqlcmd -query "SELECT name FROM sys.tables" -Database "$db" -ServerInstance "$server"
foreach ($table in $tables)
	$tableName = $table["name"]
	write-host -ForegroundColor Green "Creating File $tableName.csv"
	Invoke-Sqlcmd -Query "SELECT * FROM $tableName;" -Database "$db" -Server "$server" |
		Export-Csv -NoTypeInformation -Path "$destDir\$tableName.csv" -Encoding UTF8