@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!!
— Mr Sunshine aka 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.
- 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.