copy data from db to db table by table

  • When trying to refresh a TEST database from PROD where the PROD version is a higher release, so can't do a restore is there

    any easy way to to copy the table data to test when all the tables have a Identity Column and a TimeStamp column.

    Any examples?

    Thanks.

  • Nope - no easy way.  You will need some project to identify the tables and determine what process will be needed for each table.  This can be done using SSIS - but it could also be done with Powershell (SqlBulkCopy) or BCP or a custom .NET program or other processes.

    You also need to identify what data to extract - and what data will be anonymized or masked or removed.  And be careful of legal requirements if they apply to your system/data.

    BTW - why would you have a test environment on a lower release from production?  You cannot do any testing and validation in that environment that could ever be migrated to production, and if you did - there is no way you can be sure the changes would not cause issues.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • The copy will be ALL data from PROD copied back to TEST.. The issue is how to code the Identity Column and a TimeStamp column as

    these wouldn't be include in the table copy populated as part of the load not in the Insert Into piece...

    Test is looking to get upgraded soon.. to make SQL version match.

    Thanks.

     

  • This script works well except for handling the Identity Columns and TimeStamp column an instead of feeding it a Table Name I would like it just to read from schema and do that automatically.

    Thanks.

    Param (
    [parameter(Mandatory = $true)]
    [string] $SrcServer,
    [parameter(Mandatory = $true)]
    [string] $SrcDatabase,
    [parameter(Mandatory = $true)]
    [string] $SrcTable,
    [parameter(Mandatory = $true)]
    [string] $DestServer,
    [string] $DestDatabase, # Name of the destination database is optional. When omitted, it is set to the source database name.
    [string] $DestTable, # Name of the destination table is optional. When omitted, it is set to the source table name.
    [switch] $Truncate # Include this switch to truncate the destination table before the copy.
    )
    Function ConnectionString([string] $ServerName, [string] $DbName)
    {
    "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
    }
    ########## Main body ############
    If ($DestDatabase.Length –eq 0) {
    $DestDatabase = $SrcDatabase
    }
    If ($DestTable.Length –eq 0) {
    $DestTable = $SrcTable
    }
    If ($Truncate) {
    $TruncateSql = "TRUNCATE TABLE " + $DestTable
    Sqlcmd -S $DestServer -d $DestDatabase -Q $TruncateSql
    }
    $SrcConnStr = ConnectionString $SrcServer $SrcDatabase
    $SrcConn = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
    $CmdText = "SELECT * FROM " + $SrcTable
    $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
    $SrcConn.Open()
    [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
    Try
    {
    $DestConnStr = ConnectionString $DestServer $DestDatabase
    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    $bulkCopy.DestinationTableName = $DestTable
    $bulkCopy.BatchSize = 10000
    $bulkCopy.BulkCopyTimeout = 0
    $bulkCopy.WriteToServer($sqlReader)

    }
    Catch [System.Exception]
    {
    $ex = $_.Exception
    Write-Host $ex.Message
    }
    Finally
    {
    Write-Host "Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer"

    $SqlReader.close()
    $SrcConn.Close()
    $SrcConn.Dispose()
    $bulkCopy.Close()
    }

    • This reply was modified 1 month, 1 week ago by  Bruin.
  • any thoughts of how to modify to meet requirements?

     

    Thx.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply