9/19/2023 0 Comments Powershell tableedit sql table#the output of this process to $ScriptLogĬopy-DbaDbTableData -SQLInstance $SrcServer -Destination $DestServer -Database $SrcDatabase -DestinationDatabase $DestDatabase -Table $TableName.TableName -DestinationTable $TableName.TableName > $ScriptLogįirst of all, the source database needs to be created. #For each entry in the $TableNames array, output the table name, copy the table data between source and target and write $TableNames = Read-SQLTableData -ServerInstance $SrcServer -DatabaseName $SrcDatabase -SchemaName "dbo" -TableName "TableList" #Read the data from TableList and store in in the $TableNames array $ScriptLog = $LogLocation+$SrcDatabase+"_"+$DestDatabase+".log" #Mandatory parameters for source server, database, destination server, database and log file location #Copy-DbaDbTableData is in the 'DBATools' module #Read-SQLTableData is in the 'SQLServer' module. PowerShell Codeīelow, is the listing of the PowerShell code. The description for this cmdlet contains the magic phrase “streaming a copy of the data in the most speedy and least resource-intensive way” – precisely what I was after. The second library is dbatools, which contains the Cmdlet Copy-DbaDbTableData. The output from this can be stored into an array for later processing. Read-SQLTableData reads data from a table. As this is a task I require against SQL Server, it seemed a logical starting point. The first library to look in is the ‘SQLServer’ library. That can also be a problem with PowerShell too, in that you have to find them first. One of the delights of PowerShell, is the large number of libraries out there – there’s a very good chance that somebody has already provided a ‘cmdlet’ that does the type of thing that you’re after. Copy the contents of the tables specified in the previous step, to the destination database.Read the list of required tables, from a table within the source database.Parameters to specifiy the source and destination servers and databases.The requirements for this task were fairly simple: You wouldn’t want to follow the meanderings of my mind. I’m not going to describe the days of scouring the Internet with a variety of search engines, to find something that I understood, was suitable and as simple as possible. It also means there’s less chance that I’ll lose the code if I should require it again, in the future. What was apparent, is that there are quite a few people trying to solve the same task, so I’m posting my basic solution here, on the chance that it might be of help to somebody. Some of these were from articles that were quite old, so I hoped that new commands and libraries had since been developed, that were more suitable. I found several examples of processes to achieve this, but most of them appeared to be overly complex. Therefore the process needs to cater for an unspecified number of tables. At this time, the names of the tables are unknown and being early in the project, it is always possible that tables could be added to the requirement. BackgroundĪ lightweight process is required to copy data from several tables from a database on one instance, to a database on another. As with any tool that you don’t use regularly, more than half of the battle is finding the correct commands and methodology. PowerShell seemed to be the best fit, for my purposes. I needed a process that could copy the contents of a specific list of tables, from one database to another.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |