Export Schema and Table Data using Powershell

$servername = "servername\instancename";

$currentDate = Get-Date -Format "yyyyMMdd";

#Variables - details of the connection, stored procedure and parameters

$connectionString = "server=$servername;trusted_connection=true;";

$sqlQuery = "SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');";

#$param1Value = "SomeValue";

#SQL Connection - connection to SQL server

$sqlConnection = new-object System.Data.SqlClient.SqlConnection;

$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

$sqlCommand.Connection = $sqlConnection;

$sqlCommand.CommandText = $sqlQuery;

#$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

#SQL Adapter - get the results using the SQL Command

$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

$sqlAdapter.SelectCommand = $sqlCommand;

$dataSet = new-object System.Data.Dataset;

$recordCount = $sqlAdapter.Fill($dataSet) ;

#Close SQL Connection

$sqlConnection.Close();

#Get single table from dataset

$data = $dataSet.Tables[0];

#Loop through each row of data

foreach($row in $data)

{

    #BEGIN - Export Schema

    $dbName = $row.name;

    $schemaQuery = "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;";

    $outFile = "D:\DWExport\Schema $dbName $currentDate.bcp";

    $bcpSchemaCmd = "bcp `"$schemaQuery`" queryout `"$outFile`" -T -w -S $servername -d $dbName";

    Invoke-Expression $bcpSchemaCmd;

    #END - Export Schema

    #BEGIN - Export Table Data

    $connectionString = "server=$servername;database='$dbName';trusted_connection=true;";

    $sqlQuery = "SELECT s.name+'.'+t.name AS table2PartName FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;";

    #SQL Connection - connection to SQL server

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection;

    $sqlConnection.ConnectionString = $connectionString;

    #SQL Command - set up the SQL call

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

    $sqlCommand.Connection = $sqlConnection;

    $sqlCommand.CommandText = $sqlQuery;

    #$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

    #SQL Adapter - get the results using the SQL Command

    $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

    $sqlAdapter.SelectCommand = $sqlCommand;

    $dataSet = new-object System.Data.Dataset;

    $recordCount = $sqlAdapter.Fill($dataSet) ;

    #Close SQL Connection

    $sqlConnection.Close();

    #Get single table from dataset

    $data = $dataSet.Tables[0];

    #Loop through each row of data

    foreach($row in $data)

    {

        $table2PartName = $row.table2PartName;

        $outFile = "D:\DWExport\$dbName.$table2PartName $currentDate.bcp";

        $bcpTableQueryCmd = "bcp $table2PartName out `"$outFile`" -T -w -S $servername -d $dbName";

        Invoke-Expression $bcpTableQueryCmd;

    }

}