@SQLMonkeyNYC asked on Twitter this morning:
#sqlhelp Does anyone have a product that will back up a single table and restore it to another database? Thanks in advance!!
— My friends call me Gandalf (@SQLMonkeyNYC) November 14, 2018
Pat Phelan replied, suggesting that dbatools can do it, but after thinking on it for a bit and poking at a few functions, I realized that it’s not possible with a single function (yet). No worries. We can do it in three lines for now.
|$SourceServer = "MYSOURCE";|
|$SourceDB = "MySourceDB";|
|$DestDB = "MyDestDB";|
|$TableName = "MyTable";|
|# Get the table definition from the source|
|$tablescript = Get-DbaDbTable -ServerInstance $SourceServer -Database $SourceDB -Table $TableName | Export-DbaScript -Passthru;|
|# Run the script to create the table in the destination|
|Invoke-DbaQuery -Query $tablescript -ServerInstance $SourceServer -Database $DestDB;|
|# Copy the data|
|Copy-DbaDbTableData -ServerInstance $SourceServer -Database $SourceDB -DestinationDatabase $DestDB -Table $TableName -DestinationTable $TableName;|
- Grab the table from the source database and export the create script. I had to use
Export-DbaScriptwill create a file. Not the worst thing, but writing the file and the reading it immediately afterwards is a bit messy. Although now that I write that out, I suppose I could capture the filename that’s output and pass that to
-QueryFilein the next step. Gotta love PowerShell – there’s always a few ways to do things.
- Take the
CREATE TABLEscript that we just exported from the source database and run it against the destination.
Copy-DbaDbTableDatato bring the data over from the source to the destination.
This is a really quick & dirty example where I’m copying between two databases on the same instance, but should give you the idea. You can copy across instances and copy multiple tables as well.
Cláudio Silva (blog | twitter) has written a post that expands on the above code to include scripting out other objects related to the table being copied. The code above overlooks keys, constraints and more. Check it out!