¿Alguna vez te has encontrado en una situación en la que necesitabas restaurar tablas en SQL Server porque alguien eliminó accidentalmente más datos de los previstos? Si es así, no estás solo. En estos casos, el uso de dbatools, un potente módulo de PowerShell para SQL Server, puede facilitarte mucho la vida.

Cuando te enfrentas a la tarea de restaurar tablas, es posible que inicialmente consideres utilizar la función “Importar/Exportar datos” de SSMS. Si bien esto puede funcionar para tareas puntuales, carece de flexibilidad y reutilización. ¿Qué sucede si necesitas volver a ejecutar la tarea o compartirla con tus colegas? Aquí es donde resulta útil utilizar un script de PowerShell con dbatools.

Supongamos que ya has restaurado la base de datos de origen utilizando la copia de seguridad solicitada. Ahora, adentrémonos en el proceso de copiar tablas utilizando dbatools.

Encontrar el comando adecuado

dbatools proporciona una amplia gama de comandos para diversas tareas de SQL Server. Para encontrar el comando apropiado para copiar datos de tablas, puedes utilizar el cmdlet Find-DbaCommand. Por ejemplo, puedes buscar comandos relacionados con la copia de datos de tablas utilizando el patrón Copy*Table*Data.

Find-DbaCommand -Pattern Copy*Table*Data

A partir de los resultados, encontrarás varios comandos que coinciden con el patrón. Al leer la sinopsis de cada comando, podrás identificar el que se ajuste a tus necesidades.

Familiarizarse con el comando

Antes de utilizar un comando, siempre es recomendable explorar sus parámetros disponibles y ejemplos. Puedes utilizar el cmdlet Get-Help para obtener información detallada sobre un comando específico. Por ejemplo, puedes utilizar Get-Help Copy-DbaDbTableData -Detailed para obtener más información sobre el comando Copy-DbaDbTableData.

Copiar datos de tabla

Ahora, intentemos copiar los datos de una tabla a otra utilizando el comando Copy-DbaDbTableData. Debes proporcionar los parámetros necesarios, como las instancias de SQL de origen y destino, las bases de datos, las tablas y las tablas de destino.

$params = @{
    SqlInstance = 'sql1'
    Destination = 'sql2'
    Database = 'db1'
    DestinationDatabase = 'db2'
    Table = '[dbo].[Table]'
    DestinationTable = '[dbo].[Table]'
}
Copy-DbaDbTableData @params

Si la tabla de destino no existe, el comando mostrará una advertencia. En esos casos, puedes utilizar el parámetro -AutoCreateTable para crear automáticamente la tabla en el destino.

$params = @{
    SqlInstance = 'sql1'
    Destination = 'sql2'
    Database = 'db1'
    DestinationDatabase = 'db2'
    Table = '[dbo].[Table]'
    DestinationTable = '[dbo].[Table]'
    AutoCreateTable = $true
}
Copy-DbaDbTableData @params

Al agregar el parámetro -AutoCreateTable, el comando creará la tabla de destino si aún no existe, basándose en el script “Exportar…” de la tabla de origen.

Manejo de la estructura y los datos de la tabla

Aunque los pasos anteriores pueden ser suficientes para tablas sin índices, disparadores o restricciones, es posible que te encuentres con desafíos adicionales si las tablas tienen una estructura más compleja. Por ejemplo, si las tablas tienen claves primarias, restricciones o índices, el enfoque anterior puede no ser suficiente.

Para manejar este tipo de escenarios, puedes utilizar el cmdlet New-DbaScriptingOption para generar un objeto de opciones de script. Este objeto te permite especificar opciones adicionales para exportar e importar estructuras de tablas.

Por ejemplo, puedes establecer propiedades como DriPrimaryKey, DriForeignKeys, DriUniqueKeys, DriClustered, DriNonClustered, DriChecks y DriDefaults en $true para incluir estos elementos en la estructura de la tabla.

Aquí tienes un ejemplo de cómo puedes utilizar el cmdlet New-DbaScriptingOption y combinarlo con el comando Copy-DbaDbTableData para manejar la estructura y los datos de la tabla:

$SourceServer = "SQL1";
$DestinationServer = "SQL2"
$SourceDB = "srcDB";
$DestinationDB = "dstDB";
$tables = "Table1", 'Table2', "Table3";
$options = New-DbaScriptingOption
$options.DriPrimaryKey = $true
$options.DriForeignKeys = $true
$options.DriUniqueKeys = $true
$options.DriClustered = $true
$options.DriNonClustered = $true
$options.DriChecks = $true
$options.DriDefaults = $true

$tables | ForEach-Object {
    $tableScript = Get-DbaDbTable -ServerInstance $SourceServer -Database $SourceDB -Table $_ | Export-DbaScript -ScriptingOptionsObject $options -Passthru;
    if (-not [string]::IsNullOrEmpty($tableScript)) {
        if ($null -eq (Get-DbaDbTable -ServerInstance $DestinationServer -Database $DestinationDB -Table $_)) {
            Invoke-DbaQuery -Query $tableScript -ServerInstance $DestinationServer -Database $DestinationDB;
        }
        else {
            Write-Warning "La tabla $_ ya existe en la base de datos de destino. Se continuará y se copiarán los datos."
        }
        Copy-DbaDbTableData -ServerInstance $SourceServer -Database $SourceDB -DestinationDatabase $DestinationDB -KeepIdentity -Truncate -Table $_ -DestinationTable $_;
    }
    else {
        Write-Warning "La tabla $_ no existe en la base de datos de origen."
    }
}

Este script realiza los siguientes pasos:

1. Crea un nuevo objeto de opciones de script utilizando New-DbaScriptingOption y establece varias propiedades en $true para incluir claves primarias, claves foráneas, claves únicas, índices agrupados, índices no agrupados, comprobaciones y valores predeterminados en la estructura de la tabla.
2. Para cada tabla especificada en la lista $tables, genera el script T-SQL para la tabla de origen utilizando Export-DbaScript y las opciones de script especificadas.
3. Si la tabla no existe en la base de datos de destino, ejecuta el script generado utilizando Invoke-DbaQuery para crear la tabla.
4. Finalmente, utiliza Copy-DbaDbTableData para copiar los registros de la tabla de origen a la tabla de destino, truncando la tabla de destino y manteniendo los valores de identidad.

Ten en cuenta que si las tablas tienen relaciones entre sí, debes especificar la lista de tablas en un orden específico para asegurarte de que las tablas padre se creen antes que las tablas hijas.

Conclusión

dbatools es un conjunto de herramientas potente para la administración y automatización de SQL Server. Cuando se trata de copiar tablas, es esencial elegir el comando adecuado y comprender sus limitaciones. Si bien el comando Copy-DbaDbTableData puede manejar la copia básica de tablas, combinarlo con New-DbaScriptingOption te permite incluir elementos adicionales como claves primarias, restricciones e índices en la estructura de la tabla.

Aprovechando las capacidades de dbatools y PowerShell, puedes manejar eficientemente tareas de restauración de tablas y garantizar la integridad de los datos en tu entorno de SQL Server.

¡Gracias por leer!