DBATOOLS - Copy Data from table and create on DestinationDatabase

Question

DbaTools Related - https://dbatools.io/functions/

Hello, I am trying to find a solution to copy a table and create it on the -Destination / -DestinationDatabase, please.

I am using:

Get-DbaTable -SqlInstance "Machine1" -Database DBA -Table "Table01" | Copy-DbaTableData -Destination "Machine2\PANDA" -DestinationDatabase PANDA01 -DestinationTable "Table01"

But the since the table is not created on the destination, I get error message:

WARNING: [15:25:58][Copy-DbaTableData] Table01 does not exist on destination

Is there any way I copy and create the table on DestinationDatabase, please?

Answer 1

This is something which I had used for a similar task -

# Configuration for scripting options: Which related objects should also be scripted? 
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions; 
$so.DriAllConstraints = $true; 
$so.DriAllKeys = $true;
$so.DriClustered = $true; 
$so.DriDefaults = $true; 
$so.DriIndexes = $true; 
$so.DriNonClustered = $true; 
$so.DriPrimaryKey = $true; 
$so.DriUniqueKeys = $true; 
$so.AnsiFile = $true; 
$so.ClusteredIndexes  = $true; 
$so.IncludeHeaders = $true; 
$so.Indexes = $true; 
$so.SchemaQualify = $true; 
$so.Triggers = $true; 
$so.XmlIndexes = $true; 

#Hard-Coding the Server and database info
$SourceServer = "SourceServer"
$SourceDatabase = "SourceDatabase"
$TargetServer = "TargetServer"
$TargetDatabase = "TargetDatabase"


#Creating folders for storing Create_Database text files if they don't exist
If(!(Test-Path E:\ScriptDatabases)) 
{
    New-Item -ItemType Directory -Force -Path E:\ScriptDatabases
}

#Creating the database connection object for Source server
$Srcsrv = new-Object Microsoft.SqlServer.Management.Smo.Server($Sourceserver);
$Srcdb = New-Object Microsoft.SqlServer.Management.Smo.Database;
$Srcdb = $Srcsrv.Databases.Item($SourceDatabase);

#Creating the database connection object for Destination server
$Destsrv = new-Object Microsoft.SqlServer.Management.Smo.Server($TargetServer);
$Destdb = New-Object Microsoft.SqlServer.Management.Smo.Database;
$Destdb = $Destsrv.Databases.Item($TargetDatabase);

foreach ($table in $Srcdb.Tables)
{
    $table.Script($so) | Out-File -FilePath E:\ScriptDatabases\$($table.Name).txt
    $CreatedbQuery = Get-Content E:\ScriptDatabases\$($table.Name).txt | Out-String
    Invoke-sqlcmd -Query $CreatedbQuery -Database $TargetDatabase -server $TargetServer
}

The above will script out all the tables from source database to text files and then read the same from text files and create the tables on target database and target server. Few things to keep in mind -

1. The script requires the database to be present in the target server. If not, the script fails. So create your target databases before executing this.
2. If there are any other schemas in your database other than dbo, then you need to create those schemas first. Else your tables for other schemas won't be created.
3. For the list of scripting options you could refer to this msdn link where you can set scripting options as $true or $false for database objects as per your requirement.

You could have this Get-DbaTable -SqlInstance "Machine1" -Database DBA -Table "Table01" | Copy-DbaTableData -Destination "Machine2\PANDA" -DestinationDatabase PANDA01 -DestinationTable "Table01" run after the tables are created or you can loop in the script after the create tables itself, so that table creation and loading of data into the tables happen simultaneously. That is entirely your choice and I leave it at your discretion. Hope this helps!