SQL Server Copy table data from higher to lower SQL version using PowerShell

I came across a scenario where in I had to move database from SQL Server 2008R2 to SQL Server 2005. As most of you must be aware that
Detach/Attach and Backup/Restore doesn’t works in this case.  The only solution is to script out the database objects and data from SQL Server 2008R2 and then run the same on SQL Server 2005. However, this isn’t simple as it looks like. Moving all objects other than table and data is tedious task if the database has lots of foreign key relationships. In this case, the parent table should be created/populated before the child table are created/populate otherwise the creation/insertion terminates with below error

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Mykey”. The conflict occurred in database “mydb”, table “dbo.mytable”, column ‘mycolumn’.

The resolution to this is to first create and insert data in parent table and then create and populate the corresponding child tables. I came up with an automated solution using PowerShell to for the same. The process assumes that table belongs to default schema dbo.

The first part is to list down tables in the order of their relationship so that parent table is placed ahead of their child tables. This is done using a recurring CTE as shown below

WITH CTE AS
(
    SELECT
        OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        0 AS Sno
    FROM
        sys.objects AS so
    WHERE
        so.type = 'U'
        AND so.is_ms_Shipped = 0
    UNION ALL
    SELECT
        OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        CTE.Sno + 1 AS Sno
    FROM
        sys.objects AS so
    INNER JOIN sys.foreign_keys AS f
        ON f.parent_object_id = so.object_id
        AND f.parent_object_id != f.referenced_object_id
    INNER JOIN CTE 
        ON f.referenced_object_id = CTE.TableID
    WHERE
        so.type = 'U'
        AND so.is_ms_Shipped = 0
)
  
SELECT DISTINCT
        oCte.SchemaName,
        oCte.TableName,
        oCte.Sno
       
    FROM
       CTE as oCte
    INNER JOIN
        (  SELECT
                SchemaName as SchemaName,
                TableName as TableName,
                TableID as TableID,
                Max(Sno) as Sno
            FROM
               CTE 
            GROUP BY
                SchemaName,
                TableName,
                TableID
        ) AS iCte
        ON oCte.TableID = iCte.TableID
        AND oCte.Sno = iCte.Sno
ORDER BY
    oCte.Sno ASC,
    oCte.TableName ASC

The next part is to iterate through the result of above query and then create and populate the tables in their relationship order.

The below PowerShell code executes the above query and gets the result in DataReader.

$SrcConn = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SrcConn.ConnectionString =  "Server=" + $SrcServer+ ";Database=" + $SrcDatabase + 
                                  ";Integrated Security=True"
$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($query, $SrcConn)
$SrcConn.Open()
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()

The next step is to iterate through data reader results and create and copy table data.

$smoserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "$SrcInstance"
$db = $smoserver.Databases[$SrcDatabase]
$oScripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($smoserver)
# Include all dri objects
$oScripter.Options.DriAll = $True
WHILE($SqlReader.Read())
{
          
     # create table on destination instance
     Execute-Query $DestServer $DestDatabase  $oScripter.Script($db.Tables[$SqlReader.GetValue(1)])
      

     # write data to destination table
      Copy-TableData $SrcServer $SrcDatabase $DestServer $DestDatabase $SqlReader.GetValue(1)
      
}# while ends

The above code initializes a SMO object $smoserver and then the scripter object $oScripter to get the table script.  The table script
is then passed to Execute-Query function which executes the script on destination database and server. The code for function Execute-Query is given below.

Function Execute-Query
{
param(
    [string]$Server,[string]$Database,[string]$query
    )
 
             
            $Constr="Server=" + $Server+ ";Database=" + $Database + 
                                ";Integrated Security=True"
            $Conn  = New-Object System.Data.SqlClient.SQLConnection($Constr)
            $Conn.Open()
            $Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand( $query,$Conn)
            $Cmd.ExecuteNonQuery()
            $Conn.Close()
}

The function Copy-TableData copies the table data from source to destination. It takes 5 arguments, the source and destination connection details and the table to copy
data from. The code for Copy-TableData function is given below.

Function Copy-TableData{
param(
    [string]$SrcInstance,
    [string]$SrcDatabase,
    [string]$DestInstance,
    [string]$DestDatabase,
    [string]$Table
    )
     
    # get source table data in datareader
    $Query="SELECT * FROM " + $Table
    $SrcConnStr = "Server=" + $SrcInstance+ ";Database=" + $SrcDatabase + 
                                ";Integrated Security=True"
    $SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
    $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($Query, $SrcConn)
    $SrcConn.Open()
    [System.Data.SqlClient.SqlDataReader] $DataReader = $SqlCommand.ExecuteReader()   
     
   # Bulk insert data into destination table
    $DestConnStr = ConnectionString $DestInstance $DestDatabase
    $bulkInsert = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr,      [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    $bulkInsert.DestinationTableName = $Table
    $bulkInsert.WriteToServer($DataReader)
    $DataReader.Close()
    $SrcConn.Close()
     
}

The function uses SqlBulkCopy object as it’s fast compared to normal insert considering the fact that there isn’t any data in destination tables. Let’s wrap this code in
a function as shown below.

Function Copy-Tables {
param
(
    [string]$SrcInstance,
    [string]$SrcDatabase,
    [string]$DestInstance,
    [string]$DestDatabase,
    [boolean]$CreateTable
)
  
$SMOserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SrcInstance
$db = $SMOserver.Databases[$SrcDatabase]
 
$oScripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($smoserver)
 $oScripter.Options.DriAll = $True
 
$query="WITH CTE AS
(
    SELECT
        OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        0 AS Sno
    FROM
        sys.objects AS so
    WHERE
        so.type = 'U'
        AND so.is_ms_Shipped = 0
    UNION ALL
    SELECT
        OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        CTE.Sno + 1 AS Sno
    FROM
        sys.objects AS so
    INNER JOIN sys.foreign_keys AS f
        ON f.parent_object_id = so.object_id
        AND f.parent_object_id != f.referenced_object_id
    INNER JOIN CTE 
        ON f.referenced_object_id = CTE.TableID
    WHERE
        so.type = 'U'
        AND so.is_ms_Shipped = 0
)
  
SELECT DISTINCT
        oCte.SchemaName,
        oCte.TableName,
        oCte.Sno
       
    FROM
       CTE as oCte
    INNER JOIN
        (  SELECT
                SchemaName as SchemaName,
                TableName as TableName,
                TableID as TableID,
                Max(Sno) as Sno
            FROM
               CTE 
            GROUP BY
                SchemaName,
                TableName,
                TableID
        ) AS iCte
        ON oCte.TableID = iCte.TableID
        AND oCte.Sno = iCte.Sno
ORDER BY
    oCte.Sno ASC,
    oCte.TableName ASC
"
  $SrcConn = New-Object -TypeName System.Data.SqlClient.SqlConnection
  $SrcConn.ConnectionString =  "Server=" + $SrcServer+ ";Database=" + $SrcDatabase + 
                                ";Integrated Security=True"
  $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($query, $SrcConn)
  $SrcConn.Open()
  [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
 
WHILE($SqlReader.Read())
{
     #$DatabaseObjects += $db.Tables[$SqlReader.GetValue(1)]
     $oScripter.Script($db.Tables[$SqlReader.GetValue(1)])
      
     # create table on destination instance
     Execute-Query $DestServer $DestDatabase  $oScripter.Script($db.Tables[$SqlReader.GetValue(1)])
      
     # write data to destination table
     Copy-TableData $SrcServer $SrcDatabase $DestServer $DestDatabase $SqlReader.GetValue(1)
      
}# while ends
 
} # END Copy-Tables

In order to test the above solution run the below script to prepare test scenario.

Create database copydb1;
GO
Create database copydb2;
GO
use CopyDB1
GO
create table t2 (sno int identity Primary key, col1 char(1))
GO
create table t1 (Sno int references t2(sno),col2 char(1))
GO
insert into t2 values('A'),('B'),('C')
GO
insert into t1 values(1,'D'),(2,'E'),(3,'F')
GO
create table t3 (sno int identity Primary key, col1 char(1))
GO
create table t4 (Sno int references t3(sno),col2 char(1))
GO
insert into t3 values('A'),('B'),('C')
GO
insert into t4 values(1,'D'),(2,'E'),(3,'F')

When done, execute the powershell function to automatically copy all tables and data from copydb1 to copydb2.

Copy-Tables -SrcServer "." -SrcDatabase "Copydb1" -DestServer "." -DestDatabase "Copydb2"

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook