copy data from db to db table by table

Question

copy data from db to db table by table

Bruin

SSCrazy

Points: 2091
More actions
May 8, 2021 at 11:56 pm

#3881015

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.

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

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

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Bruin SSCrazy Points: 2091 More actions · Answer 2

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.

Bruin SSCrazy Points: 2091 More actions · Answer 3

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 3 years, 8 months ago by Bruin.

Bruin SSCrazy Points: 2091 More actions · Answer 4

any thoughts of how to modify to meet requirements?

Thx.