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;

    }

}

BCP export data and schema from all tables in all databases

 EXEC master.dbo.sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1;

RECONFIGURE WITH OVERRIDE;

GO

SET NOCOUNT ON;

DECLARE @bcpSchema nvarchar(4000), @db sysname;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

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

OPEN cur

WHILE 1 = 1

BEGIN

    FETCH cur INTO @db;

    IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpSchema = N'bcp "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;" queryout "D:\DWExport\Schema ' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @db + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME + ' -d ' + @db;

EXEC master..xp_cmdshell @bcpSchema;

END

CLOSE cur

DEALLOCATE cur

/* EXPORT SCHEMA FINISH */

/* EXPORT TABLE DATA START */

DECLARE @allTables table (completeTableName nvarchar(4000));

DECLARE @bcpTableData nvarchar(4000), @completeTableName nvarchar(4000);

INSERT @allTables (completeTableName)

EXEC sp_msforeachdb 'USE [?];

IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''ReportServer'', ''ReportServerTempDB'')

BEGIN

SELECT ''?''+''.''+s.name+''.''+t.name

FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;

END';

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT completeTableName FROM @allTables ORDER BY completeTableName;

OPEN cur

WHILE 1 = 1

BEGIN

FETCH cur INTO @completeTableName;

IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpTableData = N'bcp ' + @completeTableName + ' out "D:\DWExport\' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @completeTableName + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME;

EXEC master..xp_cmdshell @bcpTableData;

END

CLOSE cur

DEALLOCATE cur