May 8, 2021 at 11:56 pm
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.
May 9, 2021 at 3:37 pm
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
May 9, 2021 at 4:57 pm
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.
May 9, 2021 at 5:23 pm
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()
}
May 10, 2021 at 10:50 pm
any thoughts of how to modify to meet requirements?
Thx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply