Início / dba / Perguntas / 30772
Accepted
Iain Samuel McLean Elder
Asked: 2012-12-20 10:57:14 +0800 CST

Por que estou perdendo alguma saída?

  • 772

Arquivamento para evitar o esgotamento do ID

Estamos prestes a ficar sem IDs em uma das tabelas do nosso sistema de registro OLTP. É meu trabalho encontrar uma maneira de 'arquivar' os dados de linha existentes na tabela completa e em todas as tabelas de referência para que possamos continuar registrando novos dados nas tabelas.

A maneira mais rápida de arquivar dados em uma tabela é simplesmente renomear a tabela de destino e todos os objetos dependentes -- tabelas de junção, índices, restrições. É rápido e mantém os dados intactos. Para terminar o trabalho, temos que criar novas cópias vazias de todos os objetos com seus nomes antigos. Se fizermos todas essas operações em uma transação, os procedimentos armazenados que inserem novos dados não falharão devido à falta de tabelas.

Uma tentativa de resolver usando o PowerShell

Montei um script do PowerShell para gerar um script T-SQL para executar a operação de arquivamento.

O script não está gerando tantas instruções de renomeação quanto deveria e não entendo o porquê.

O script carrega o SMO, define os nomes dos objetos de destino e configura uma conexão somente de captura com o servidor - isso é para que eu possa capturar comandos de renomeação para inspeção posterior:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'CLOUDCORP\LOGGING'
$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

Em seguida, ele cria uma matriz de objetos que serão arquivados por renomeação. A própria tabela de destino, todas as tabelas de referência e todas as chaves e todos os índices dessas tabelas devem ser arquivados:

$Database = $Server.Databases[$DatabaseName]

$Table = @($Database.Tables[$TableName, $TableSchemaName])

$ReferencingTables = $Database.Tables.ForeignKeys |
    ? { $_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName } |
    % { $_.Parent } | Sort ID | Get-Unique

$TablesToArchive = $Table + $ReferencingTables

$ObjectsToArchive = $TablesToArchive + $TablesToArchive.Indexes + $TablesToArchive.ForeignKeys

A parte do problema é a próxima. Para cada objeto a ser arquivado, tento capturar uma instrução de renomeação.

$ObjectsToArchive | % { $_.Rename($_.Name + '_archive') }
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text

Saída inesperada

Depois de executar isso no meu banco de dados, a matriz $ObjectsToArchivecontém 48 itens. A expressão $objectsToArchive.Nameproduz uma lista como esta:

tbDataRequests
tbDataRequestPenguinServers
tbDataRequestLegs
tbDataRequestPaidDataRequests
tbDataRequestResponses
tbDataRequestUUIDMappings
tbDataRequestRouteNodes
tbRequestDataRequestQ
tbRequestDataRequests
IX_DataRequests_DataRequestDT
PK_DataRequests
PK_DataRequestPenguinServers
PK_DataRequestLegs
IX_DataRequestPaidDataRequests_PaidDataRequestID
PK_DataRequestPaidDataRequests
IX_DataRequestResponses_ScrapeResponseID
PK_DataRequestResponses
IX_DataRequestUUIDMappings_DataRequestID
PK_DataRequestUUIDMappings
PK_Geo_DataRequestRouteNodes
IX_RequestDataRequestQ_FK_RequestUUIDID
PK_RequestDataRequestQ
IX_RequestDataRequests_DataRequestID
PK_RequestDataRequests
FK_DataRequests_OrderType_OrderTypeID
FK_DataRequests_DatePairs_DatePairID
FK_DataRequests_CustomerCounts_CustomerCountID
FK_DataRequests_Routes_RouteID
FK_DataRequests_DataClients_DataClientID
FK_DataRequests_UserCountries_UserCountryID
FK_DataRequests_Websites_WebsiteID
FK_DataRequestPenguinServers_PenguinServers_JacqiardServerID
FK_DataRequestPenguinServers_DataRequests_DataRequestID
FK_DataRequestLegs_DatePairs_DatePairID
FK_DataRequestLegs_LegTypes_LegTypeID
FK_DataRequestLegs_Routes_RouteID
FK_DataRequestLegs_DataRequests_DataRequestID
FK_DataRequestPaidDataRequests_PaidDataRequests_PaidDataRequestID
FK_DataRequestPaidDataRequests_DataRequests_DataRequestID
FK_DataRequestResponses_DataRequests_DataRequestID
FK_DataRequestResponses_ScrapeResponses_ScrapeResponseID
FK_DataRequestUUIDMappings_DataRequests_DataRequestID
FK_DataRequestUUIDMappings_DataRequestUUIDs_Scrape RequestUUIDID
FK_Geo_DataRequestRouteNodes_DataRequests_DataRequestID
FK_RequestDataRequestQ_RequestUUIDs_RequestUUIDID
FK_RequestDataRequestQ_DataRequests_DataRequestID
FK_RequestDataRequests_Requests_RequestID
FK_RequestDataRequests_DataRequests_RequestID

Deve haver uma instrução de renomeação para cada objeto neste array. No entanto, a propriedade CapturedSql contém apenas 10 instruções de renomeação. A expressão $RenameCommands | ? { $_ -like '*sp_rename*' }produz uma lista como esta:

EXEC dbo.sp_rename @objname = N'[Logging].[tbDataRequests]', @newname = N'tbDataRequests_archive', @objtype = N'OBJECT'
EXEC sp_rename N'[Logging].[tbDataRequests].[IX_DataRequests_DataRequestDT]', N'IX_DataRequests_DataRequestDT_archive', N'INDEX'
EXEC sp_rename N'[Logging].[tbDataRequests].[PK_DataRequests]', N'PK_DataRequests_archive', N'INDEX'
EXEC sp_rename N'[FK_DataRequests_OrderType_OrderTypeID]', N'FK_DataRequests_OrderType_OrderTypeID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DatePairs_DatePairID]', N'FK_DataRequests_DatePairs_DatePairID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_CustomerCounts_CustomerCountID]', N'FK_DataRequests_CustomerCounts_CustomerCountID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Routes_RouteID]', N'FK_DataRequests_Routes_RouteID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DataClients_DataClientID]', N'FK_DataRequests_DataClients_DataClientID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_UserCountries_UserCountryID]', N'FK_DataRequests_UserCountries_UserCountryID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Websites_WebsiteID]', N'FK_DataRequests_Websites_WebsiteID_archive', N'OBJECT'

Ele está criando scripts de instruções de renomeação para Logging.tbDataRequests e suas chaves e índices, mas não para nenhum dos outros objetos.

O que eu estou fazendo errado aqui?

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Você não pode percorrer objetos da maneira que você codificou. Aqui está o código de trabalho:

    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

$Database = $Server.Databases[$DatabaseName]

$Table = $Database.Tables[$TableName, $TableSchemaName]

$TablesToArchive = @($Table)
$Database.Tables | Select -ExpandProperty ForeignKeys | % {
    if($_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName)
        { $TablesToArchive += $_.Parent }
}
# $TablesToArchive | % { $_.Name }

$ObjectsToArchive = ($TablesToArchive | Select -ExpandProperty Indexes) + ($TablesToArchive | Select -ExpandProperty ForeignKeys)
# $ObjectsToArchive | % { $_.Name }

$ObjectsToArchive | % {
    $_.Rename($_.Name + '_archive')
}
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text
# $RenameCommands

    Por exemplo, você estava esperando

    $Database.Tables.ForeignKeys

    Para lhe dar uma coleção combinada ForeignKeyscomposta por ForeignKeyscada Tablesobjeto. Se você adicionou os comandos debug

    # $ObjectsToArchive | % { $_.Name }

    Você teria sido capaz de rastreá-lo muito rapidamente.

    • 6

relate perguntas