将 PowerShell 脚本从"调用"更改为"大容量复制"

Question

我已经使用了下面的代码一段时间，主要是为了测试它来理解它。我现在真正想做的是通过使用批量复制来提高它的速度。

该代码从一系列实例中获取数据库和表信息，这些实例保存在服务器上的文本文件中，然后通过调用将收集的数据添加到表中。

问题是它为每个数据库和每个表发送数据，如果可能的话，一次发送一个。

我看过许多提供相关信息的网站/博客，但它们似乎都不是我需要的。

$Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$today = Get-Date
$srvlist = @(Get-Content "c:scriptstablegrowth.TXT")
foreach ($server in $srvlist) {
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$dbs = $srv.Databases
foreach ($db in $dbs) {
if ($db.IsAccessible) {
$name1 = $db.name
$size1 = $db.size
$dbspace1 = $db.DataSpaceUsage/1KB
$dbindexsp1 = $db.IndexSpaceUsage/1KB
$dbspaceavail1 = $db.SpaceAvailable/1KB
#Write-Host "dbname=" $name1 $size1 $dbspace1 $dbindexsp1 $dbspaceavail1 $db.IsAccessible
switch ($name1) {
'master' {}
'msdb' {}
'model'  {}
'SSISDB'  {}
'SSRSData'  {}
'Northwind' {}
'tempdb' {}
default {
Invoke-Sqlcmd -ServerInstance "******" -Database "DBAMonitoring" -Query "INSERT INTO is_sql_databases VALUES ('$server','$name1','$today',$dbspace1,$dbspaceavail1,$dbindexsp1) "
foreach ( $tbl in $db.tables) {
$tname1    = $tbl.Name
$tindexsp1 = $tbl.IndexSpaceUsed/1KB
$trows1    = $tbl.RowCount
$tspace1   = $tbl.DataSpaceUsed/1KB
#Write-Host "tbl name=" $tname1 $tindexsp1 $trows $tspace1
if ($trows1 -gt 500) {
Invoke-Sqlcmd -ServerInstance "****" -Database "DBAMonitoring" -    Query "INSERT INTO is_sql_tables VALUES ('$server','$name1','$tname1','$today',$trows1,$tspace1,$tindexsp1) "
} ####end RowCount > 0
} #####end Row loop
} ###### end Default
} #####end switch
}  #####end IsAccessible
}  #####end database loop
} ###end server loop
$Stopwatch.Stop()
$Stopwatch.Elapsed.TotalSeconds
$Stopwatch.Elapsed.TotalMinutes

Answer 1

一种方法是将行加载到DataTable中，并直接在脚本中使用.NET SqlBulkCopy进行大容量插入。只需更改以下示例中的 DataTable 列名称和数据类型，以匹配实际表的名称和数据类型。

$dt = New-Object System.Data.DataTable;
[void]($dt.Columns.Add("server_name", [System.Type]::GetType("System.String")).MaxLength = 128)
[void]($dt.Columns.Add("name1", [System.Type]::GetType("System.String")).MaxLength = 128)
[void]($dt.Columns.Add("tname1", [System.Type]::GetType("System.String")).MaxLength = 128)
[void]($dt.Columns.Add("today", [System.Type]::GetType("System.DateTime")))
[void]($dt.Columns.Add("trows1", [System.Type]::GetType("System.Int64")))
[void]($dt.Columns.Add("tspace1", [System.Type]::GetType("System.Int64")))
[void]($dt.Columns.Add("tindexsp1", [System.Type]::GetType("System.Int64")))
$Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$today = Get-Date
$srvlist = @(Get-Content "c:scriptstablegrowth.TXT")
foreach ($server in $srvlist) {
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$dbs = $srv.Databases
foreach ($db in $dbs) {
if ($db.IsAccessible) {
$name1 = $db.name
$size1 = $db.size
$dbspace1 = $db.DataSpaceUsage/1KB
$dbindexsp1 = $db.IndexSpaceUsage/1KB
$dbspaceavail1 = $db.SpaceAvailable/1KB
#Write-Host "dbname=" $name1 $size1 $dbspace1 $dbindexsp1 $dbspaceavail1 $db.IsAccessible
switch ($name1) {
'master' {}
'msdb' {}
'model'  {}
'SSISDB'  {}
'SSRSData'  {}
'Northwind' {}
'tempdb' {}
default {
# Invoke-Sqlcmd -ServerInstance "******" -Database "DBAMonitoring" -Query "INSERT INTO is_sql_databases VALUES ('$server','$name1','$today',$dbspace1,$dbspaceavail1,$dbindexsp1) "
foreach ( $tbl in $db.tables) {
$tname1    = $tbl.Name
$tindexsp1 = $tbl.IndexSpaceUsed/1KB
$trows1    = $tbl.RowCount
$tspace1   = $tbl.DataSpaceUsed/1KB
#Write-Host "tbl name=" $tname1 $tindexsp1 $trows $tspace1
if ($trows1 -gt 500) {
# Invoke-Sqlcmd -ServerInstance "." -Database "tempdb" -Query "INSERT INTO is_sql_tables VALUES ('$server','$name1','$tname1','$today',$trows1,$tspace1,$tindexsp1) "
$row = $dt.NewRow()
$dt.Rows.Add($row)
$row["server_name"] = $server
$row["name1"] = $name1
$row["tname1"] = $tname1
$row["today"] = $today
$row["trows1"] = $trows1
$row["tindexsp1"] = $tindexsp1
} ####end RowCount > 0
} #####end Row loop
} ###### end Default
} #####end switch
}  #####end IsAccessible
}  #####end database loop
} ###end server loop
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy("Data Source=******;Integrated Security=SSPI;Initial Catalog=DBAMonitoring ")
$bcp.DestinationTableName = "dbo.is_sql_tables"
$bcp.WriteToServer($dt);
$Stopwatch.Stop()
$Stopwatch.Elapsed.TotalSeconds
$Stopwatch.Elapsed.TotalMinutes