En este artículo, se podrá aprender las habilidades clave que se requieren para copiar tablas entre instancias de SQL Server, incluidas las bases de datos SQL locales y la nube. En este artículo, se podrá verificar varias formas de copiar una (s) tabla (s) entre las bases de datos SQL, para visualizar los beneficios y las ventajas y desventajas de cada opción.
Introducción
Sin embargo, antes de comenzar el artículo, es importante que repasemos los objetivos del artículo. Posteriormente pasamos a la descripción general de cada módulo o métodos. En esta guía, discutimos brevemente varios aspectos de las opciones incorporadas disponibles de SQL Server, así como también le mostramos algunas herramientas de PowerShell y de terceros que pueden usarse para copiar tablas SQL entre las bases de datos y también entre las instancias. Al iniciar cada método, se le ha proporcionado suficiente información sobre los siguientes módulos. Seguimos este acápite con varios módulos, cada uno de los cuales está dedicado a métodos específicos.
Objetivos:
- Introducción
-
Discusión de los diferentes métodos para copiar tablas
- Uso de la biblioteca de clases .Net para copiar tablas con PowerShell
- Uso del asistente para importación y exportación
- Uso de sqlpackage.exe – método de extracción y publicación
- Uso del asistente Generar secuencias de comandos en SSMS (SQL Server Management Studio)
- Uso de la instrucción INSERT INTO SQL
- Y más …
Empezar
En SQL Server, el copiar tablas entre las bases de datos de las mismas instancias de SQL es relativamente más fácil que copiar los datos entre los servidores remotos. Con el fin de minimizar la carga de trabajo en la base de datos de producción, siempre se recomienda restaurar la base de datos desde la copia de seguridad a la nueva base de datos y posteriormente usar los mejores métodos para copiar los datos a la base de datos de destino. Nuevamente, esto depende de la cantidad de tablas, el tamaño y del espacio disponible. Si el tamaño de la (s) tabla (s) es más del 50% del tamaño total de la base de datos, el método de copia de seguridad y restauración será una opción recomendada.
En algunos casos, es posible que tenga que copiar algunas tablas muy grandes, y luego puede terminar moviendo las tablas para separar los grupos de archivos y realizar un método parcial de copia de seguridad y restauración para copiar los datos. A su requerimiento puede consultar el artículo Grupo (s) de archivos de base de datos y Restauraciones por partes en SQL Server para obtener más información.
También puede usar herramientas de terceros para realizar una restauración a nivel de objeto desde un archivo de copia de seguridad..
Objetivo de la Clase SqlBulkCopy para copia de datos con PowerShell
PowerShell es siempre mi primera opción para cualquier tarea administrativa. Net proporciona una biblioteca de clases SqlBulkCopy para cargar en masa las tablas en la base de datos
Puede consultar los métodos del artículo 6 para escribir la salida de PowerShell en una tabla de SQL Serverpara obtener más información sobre las bibliotecas de clases .Net.
Guion de PowerShell
El siguiente script PoSH crea una función llamada Get-SQLTable. La función tiene varios parámetros obligatorios
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
function Get-SQLTable { [CmdletBinding()] param( [Parameter(Mandatory=$true)] [string] $SourceSQLInstance, [Parameter(Mandatory=$true)] [string] $SourceDatabase, [Parameter(Mandatory=$true)] [string] $TargetSQLInstance, [Parameter(Mandatory=$true)] [string] $TargetDatabase, [Parameter(Mandatory=$true)] [string[]] $Tables, [Parameter(Mandatory=$false)] [int] $BulkCopyBatchSize = 10000, [Parameter(Mandatory=$false)] [int] $BulkCopyTimeout = 600 ) $sourceConnStr = "Data Source=$SourceSQLInstance;Initial Catalog=$SourceDatabase;Integrated Security=True;" $TargetConnStr = "Data Source=$TargetSQLInstance;Initial Catalog=$TargetDatabase;Integrated Security=True;" try { Import-Module -Name SQLServer write-host 'module loaded' $sourceSQLServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SourceSQLInstance $sourceDB = $sourceSQLServer.Databases[$SourceDatabase] $sourceConn = New-Object System.Data.SqlClient.SQLConnection($sourceConnStr) $sourceConn.Open() foreach($table in $sourceDB.Tables) { $tableName = $table.Name $schemaName = $table.Schema $tableAndSchema = "$schemaName.$tableName" if ($Tables.Contains($tableAndSchema)) { $Tablescript = ($table.Script() | Out-String) $Tablescript Invoke-Sqlcmd ` -ServerInstance $TargetSQLInstance ` -Database $TargetDatabase ` -Query $Tablescript $sql = "SELECT * FROM $tableAndSchema" $sqlCommand = New-Object system.Data.SqlClient.SqlCommand($sql, $sourceConn) [System.Data.SqlClient.SqlDataReader] $sqlReader = $sqlCommand.ExecuteReader() $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($TargetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity) $bulkCopy.DestinationTableName = $tableAndSchema $bulkCopy.BulkCopyTimeOut = $BulkCopyTimeout $bulkCopy.BatchSize = $BulkCopyBatchSize $bulkCopy.WriteToServer($sqlReader) $sqlReader.Close() $bulkCopy.Close() } } $sourceConn.Close() } catch { [Exception]$ex = $_.Exception write-host $ex.Message } finally { #Return value if any } } |
La variable de matriz $ tables se utilizará para asignar la lista de las tablas que se copiarán a la base de datos de destino.
|
1 |
[string[]] $tables = @('dbo.OPERATION','dbo.OPERATION_DETAIL') |
Invoquemos la función Get-SQLTable con los parámetros que se mencionan a continuación para copiar las tablas de la base de datos Adventureworks2016 en “HQDBT01” a la base de datos Adentureworks2012 en la instancia hqdbt01/sql2017 “.
|
1 |
Get-SQLTable -SourceSQLInstance hqdbt01 -SourceDatabase AdventureWorks2016 -TargetSQLInstance hqdbt01\sql2017 -TargetDatabase AdventureWorks2012 -Tables $tables -BulkCopyBatchSize 5000 |
Como se verá el resultado muestra las tablas OPERATION y OPERATION_DETAIL copiadas en la instancia de destino.
Asistente de importación y exportación de SSMS
Echemos un vistazo y verificamos al Asistente de importación y exportación. La interfaz es muy similar a la de todos los demás asistentes, eso le permite pasar fácilmente por un proceso y ejecutar el proceso de copia de datos escribiendo muy poco o nada de código. Para tal fin, tenemos muy pocas opciones que podamos hacer dentro del asistente. Sin embargo, para poder realizar este paso, para importar y exportar datos de una fuente a otra, esta es realmente una herramienta excelente. Si desea hacer casi cualquier tipo de transformaciones, entonces no desea usar esta herramienta, es posible que necesite usar las herramientas de datos de Visual Studio (VSDT) y hacer un flujo de datos.
Entonces podríamos empezar. Lo primero es abrir Microsoft SQL Server Management Studio (SSMS). Y utilizaremos la base de datos AdventureWorks2016, y la trasladaremos a otra instancia de SQL.
- Abra el Explorador de objetos, ubique la base de datos, haga clic con el botón derecho y seleccione Tareas y elija la opción Exportar datos
- Ahora la fuente de datos, si elimino esto, verá las diferentes fuentes que podemos usar. Vamos a utilizar SQL Native Client 11.0, el proveedor de SQL.
- A continuación, Nombre del servidor, se recomienda extraer el nombre del servidor y la base de datos en el asistente de importación y exportación utilizando la lista desplegable de selección.
- Ahora, en la selección de destino, nuevamente despliegue el proveedor SQL, el nombre del servidor y la base de datos de una lista desplegable en lugar de escribirlo. Iremos a continuación
- Al seleccionar tablas y vistas de origen, seleccione los objetos para copiar en el destino o podría escribir una consulta. Pero aquí solo vamos a copiar los datos. En este caso, traigamos dbo.Cities y Person.Address
- Haga clic en Siguiente
- Estamos listos para ejecutar el trabajo de copia. Elija Ejecutar inmediatamente y haga clic en Siguiente
- Podemos ver un resumen de la acción que se va a realizar con el asistente
- Haga clic en el botón Finalizar para ejecutar los pasos del trabajo.
- Después de la ejecución exitosa del trabajo, podemos validar y revisar el resultado.
Uso de sqlpackage.exe – método de extracción y publicación
Sqlpackage es una facilidad de línea de comandos que automatiza el proceso de extracción de “Esquema y datos” y publica el archivo generado en una base de datos de destino. La utilidad de línea de comandos SqlPackage.exe es un componente interno de las Herramientas de datos de SQL Server (SSDT).
Puede consultar los artículos Entrega continua de bases de datos (CD) utilizando las herramientas de SQL Server SqlPackage.exe y SqlPackage.exe: automatice la restauración de la base de datos de SQL Server utilizando bacpac con las técnicas PowerShell o Batchpara obtener más información.
Con Sqlpackage.exe, es posible extraer el esquema y los datos y luego publicar solo los datos de las tablas enumeradas. Mediante este proceso, los objetos como procedimientos almacenados, funciones, etc. se extraerán en el archivo. dacpac, pero se excluirán al publicar el contenido en la base de datos de destino.
Es posible enumerar las tablas. Al especificar tablas individuales, primero debe especificar / p: ExtractAllTableData = False y luego agregar la propiedad / p: TableData para especificar cada tabla en forma de Schema.Table.
El siguiente ejemplo utiliza la propiedad / p: TableData para tres tablas. Puede ver que las tablas se refieren en forma de dbo.orders.
/p:TableData=dbo.Orders
/p:TableData=Orders.Orders
/p:TableData=Person.Address
SqlPackage /Action:Extract /SourceDatabaseName:Adventureworks2016 /SourceServerName:HQDBT01 /TargetFile:F:\PowerSQL\smartbackup\AdventureWorks2016.dacpac /p:IgnoreExtendedProperties=True /p:ExtractAllTableData=FALSE /p:TableData=dbo.Cities /p:TableData=dbo.citiesDemo
A continuación, vamos a preparar la secuencia de comandos para automatizar el proceso de extracción y publicación.
- Establezca la variable MEDIO AMBIENTE. El archivo se puede encontrar en otros directorios lo que depende de la instalación SSDT. En este caso, SqlPackage.exe se encuentra en la carpeta C:\Archivos de programa (x86)\Microsoft SQL Server\140\DAC\ bin\
-
Prepare los valores de entrada
- El directorio de respaldo
- La base de datos de origen
- La Instancia de SQL Server de origen
- La base de datos de destino
- La instancia de SQL Server de destino
- Ejecute el ejecutable SqlPackage.exe con una acción de extracción en la instancia SQL de origen
- Ejecute T-SQL para encontrar la existencia de la base de datos de destino
- Ejecute SqlPackage.exe con una acción de publicación en la instancia SQL de destino
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
# Environment PATH variable $Variables=$env:PATH #Check the path existence of the SqlPackage.exe and print its status IF (-not $Variables.Contains( "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin")) { write-host "SQLPackage.exe path is not found, Update the environment variable" $ENV:PATH = $ENV:PATH + ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;" } #the input parameters $BackupDirectory="F:\PowerSQL\smartbackup\" $DatabaseName="AdventureWorks2012" #Source SQL Server Instance $SourceServerName="HQDBT01" #target SQL Instance $TargetserverName="HQDBT01\SQL2017" #Prepare the target filename $dir = [io.path]::GetDirectoryName($BackupDirectory) #set the filename, the database should be a part of the filename $filename = "AdventureWorks2012_Rpt" #extension must be dacpac $ext = "dacpac" # Prepare FULL PATH $TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext" #print FULL file path $TargetFilePath #Run the SqlPackage tool to extract the data SqlPackage /Action:Extract /SourceDatabaseName:$DatabaseName /SourceServerName:$SourceServerName /TargetFile:$TargetFilePath /p:IgnoreExtendedProperties=True /p:ExtractAllTableData=FALSE /p:TableData=dbo.Orders /p:TableData=dbo.Address #Get the latest file in a given directory $NewestBacPacFile = Get-ChildItem -Path $dirName\$filename*.$ext | Sort-Object LastAccessTime -Descending | Select-Object -First 1 #print the latest bacfile name depending the name of the database $file="$NewestBacPacFile" $FILE #If exists then drop the database $dropTSQL= @" IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = '$DatabaseName') DROP DATABASE $DatabaseName "@ #Using sqlcmd, execute the DropTSQL on the target instance. SQLCMD -S $TargetserverName -U SA -P Api1401$ -Q $dropTSQL #Publish the data in the target database using sqlpackage.exe SqlPackage.exe /a:publish /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:Api1401$ |
Salida:
En la salida, puede ver que las tablas dbo.orders y dbo.address se procesan.
Puede consultar el artículo SqlPackage.exe – Automatizar la restauración de la base de datos de SQL Server utilizando bacpac con las técnicas PowerShell o Batch para obtener más información
Generar Guiones o esquemas de datos con SQL Server Management Studio
En esta sección, se analizara otra forma de generar “esquema y datos” para objetos de bases de datos de SQL Server
A continuación, veamos los pasos para generar una secuencia de comandos SQL que incluye “Esquema y Datos”
- Conecte la instancia de SQL Server
- Abra el Explorador de objetos y encuentre la base de datos
-
Haga clic con el botón derecho en la base de datos, seleccione Tareas, y posteriormente haga clic en Generar secuencias de comandos …. Después de eso, se abre el Asistente de script. Haga clic en “Siguiente”.
-
En la páginaElegir objeto active la opción de Seleccionar objetos de base de datos específicos. Seleccione los objetos requeridos y haga clic en el botón Siguiente.
-
En Establecer opciones de secuencias de comandos, seleccione el Tipo de salida y haga clic en el botón Avanzado. En este caso, el tipo de salida redirigido a la ventana de consulta
-
En las Opciones avanzadas de secuencias de comandos, seleccione la opción “Esquema y datos” de la lista desplegable y haga clic en Aceptar.
-
A continuación, la página Resumen muestra el detalle de los esquemas de todo el proceso. Haga clic en Siguiente
-
Ahora, una vez que se encuentre en la página Guardar o publicar secuencias de comandos muestra el progreso de todo el proceso. Puede monitorear el estado de todo el esquema y el proceso de generación de datos
INSERTAR EN SQL
Esta también es una opción para clonar la tabla de la base de datos a otra.
Adicionalmente puede consultar el artículo Descripción general de la inserción de SQL para obtener más información.
Resumen
Hasta ahora, en base a todo lo explicado hemos discutido varios métodos para copiar las tablas en las bases de datos de SQL Server. Es evidente e importante que restaurar un par de tablas desde una copia de seguridad puede ser un proceso que consume tiempo y espacio. Dependerá de su entorno seguir cualquiera de los pasos antes mencionados para copiar las tablas en SQL Server. No existe una forma estándar/recomendada de copiar una tabla entre las bases de datos, pero existen muchos enfoques posibles que pueden ser utilizados para satisfacer sus necesidades.
Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.
Ver todas las publicaciones de Prashanth Jayaram
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019