...if you have a long list of names of tables to transfer, and you'd rather not having to mark their check-boxes in a wizard one by one?
6
-
what do you mean copy tables in a list? You want to copy X number of tables (and their rows) from one server to the next?S3S– S3S2018-06-07 16:30:39 +00:00Commented Jun 7, 2018 at 16:30
-
@scsimon: I have a list of table names that I need to copy from a database with even more tables to another database missing those tables. I'd rather feed that list to a script or something than use a wizard and having to mark the tables manually, since it is a long list.bitoolean– bitoolean2018-06-07 16:39:39 +00:00Commented Jun 7, 2018 at 16:39
-
So you don't want to do this method if i understand you correctly?S3S– S3S2018-06-07 16:54:37 +00:00Commented Jun 7, 2018 at 16:54
-
I won't post this as an answer, but it seems to be what you are trying to do. I'd just caution that it could take a long time depending on the data and table list. I'd break it up in batches. Also, I'd watch your transaction logs and increase their backup frequency.S3S– S3S2018-06-07 16:58:41 +00:00Commented Jun 7, 2018 at 16:58
-
@scsimon: Thank you for that information, but, as I mentioned in the title, I need to copy structure as well (create the tables anew), not just transfer data.bitoolean– bitoolean2018-06-07 17:09:10 +00:00Commented Jun 7, 2018 at 17:09
|
Show 1 more comment
1 Answer
Bitoolean,
Take a look at dbatools for powershell. Soulds like their toolset may be of use. I know they have a migration tool in there.
dbatools can be found here https://dbatools.io/
Also, You could look at https://sqljana.wordpress.com/2017/06/12/powershell-copy-sql-server-tables-structure-data-indexes-to-another-databaseinstance/ which seems to detail method in powershell to do what you wanted and have the $tables variable populated from a text file list or SQL Query.
4 Comments
bitoolean
Thank you. As far as I can see in the command list though, the DBATools is only useful for automating copying of entire databases though (Copy-DbaDatabase)?
bitoolean
dbatools seems to have commands for copying every other object, but not tables.
LinkOps
bitoolean, Take a look at this blog post [link]:(sqljana.wordpress.com/2017/06/12/…) From the looks of it you can import a list of tables into an array variable and call it directly (In the usage section the $tables variable could read from a SQL Query or text file)
bitoolean
Thank you. I will look into it. You should probably post that as an answer. For now I've resorted to using SSIS packages to at least prevent having to repeat the clicking for the same list when I have to copy the same tables more than once. The question is still valid though.