Daniel Calbimonte

Cómo utilizar el procedimiento extendido xp_cmdshell

June 5, 2017 by

Introducción

El xp_cmdshell es un procedimiento extendido muy potente que se lo utiliza para ejecutar la línea de comandos de Windows (cmd). Este comando es bastante útil para ejecutar tareas en el sistema operativo tales como copiar archivos, crear carpetas, compartir carpetas, etc. mediante T-SQL.

En este nuevo artículo, mostraremos algunos ejemplos útiles sobre cómo usarlo.

En esta ocasión, mostraremos cómo realizar las siguientes tareas:

  • Cómo habilitar el procedimiento extendido xp_cmdshell
  • Cómo realizar copiar copias de seguridad de una carpeta de origen a otra de destino utilizando xp_cmdshell
  • Cómo guardar la ruta de acceso completa de archivos de una carpeta en una tabla de SQL Server
  • Cómo ejecutar cmdlets de PowerShell utilizando T-SQL y xp_cmdshell
  • Cómo conectarse a Azure en SSMS utilizando xp_cmdshell y sqlcmd

Requisitos

  1. SQL Server instalado. En este ejemplo, estamos utilizando la versión de SQL Server 2016, pero puede trabajar con SQL Server 2005 o versiones superiores.

Iniciando los ejemplos

Cómo habilitar el procedimiento extendido xp_cmdshell

Primeramente, habilitaremos el procedimiento extendido xp_cmdshell. Necesitaremos verificar si las opciones avanzadas de SQL Server están habilitadas. Para ello, vamos a utilizar el siguiente procedimiento en SQL Server Management Studio (SSMS):

Cuando se ejecuta el comando, si el valor de configuración al ejecutar el procedimiento es igual a 0, significa que las Opciones avanzadas no están habilitadas.


Figura 1. Opciones avanzadas en SQL Server

Para activar las opciones avanzadas, configure esta opción avanzada a 1. El siguiente ejemplo muestra cómo hacerlo:

Xp_cmdshell es una de las opciones avanzadas, que ahora puede habilitar cuando la opción avanzada está en 1. El siguiente ejemplo muestra como habilitarlo:

Cómo realizar copiar copias de seguridad de una carpeta de origen a otra de destino utilizando xp_cmdshell

En el siguiente ejemplo, se muestra cómo copiar varias copias de seguridad de una carpeta de origen a otra de destino. Para este ejemplo, tenemos varias copias de seguridad en la ruta c:\backup:


Figura 2. Copias de seguridad SQL

Las siguientes instrucciones T-SQL realizarán las copias los archivos de la carpeta c:\Backup a la carpeta c:\Shared:

La salida del comando será la siguiente:


Figura 3. Copiar salida de archivos

Como puede ver, el comando copiar cmd copia los archivos a la carpeta compartida:


Copias de seguridad en la carpeta de destino

Cómo almacenar ruta de acceso completa de archivos de una carpeta a una tabla de SQL Server

El ejemplo siguiente, almacenará en una tabla de SQL Server todas las rutas de las imágenes almacenadas en un archivo especificado.

Por ejemplo, tengo las siguientes imágenes en la carpeta c: \ image:


Figura 5. Imágenes en una carpeta

Lo que deseamos hacer, es almacenar en una tabla SQL las rutas completas de las imágenes como sigue:

C:\images\1.jpg
C:\images\2.jpg
…etc.

Primeramente, almacenaremos todas las rutas de la carpeta en un archivo txt llamado path:

Path.txt ahora incluirá todas las rutas completas de la imagen:


Figura 6. Rutas completas almacenados

Es necesario tener una tabla para almacenar las rutas:

Para poder importar la información del archivo txt de la figura 6 en la tabla recién creada utilizaremos las siguientes frases:

Bcp es el comando utilizado para importar desde la ruta.txt a la tabla dbo.images en la base de datos AdventureWorks2016CTP3. -T se utiliza para conectarse mediante una conexión de confianza (la autenticación de Windows actual) y -c se utiliza para realizar operaciones con el tipo de caracteres.

Si realiza una selección en la tabla de imágenes, puede verificar que podemos importar los datos correctamente a SQL Server haciendo una selección:

La sentencia de SQL mostrará los siguientes resultados:


Figura 7. Los datos importados de un txt a SQL utilizando xp_cmdshell y bcp

Vamos a crear una tabla con ID y una columna con las columnas path y image name:

Finalmente, importaremos los datos de las imágenes de la tabla a las imágenes de la tabla:

insert into pictures (imagename,path) select replace(path,’c:\images\’,”) as imagename,path from images

Ahora podrá visualizar el nombre de la ruta y la imagen en una tabla:

select * from pictures


Figura 8. Datos de tabla importados

Cómo ejecutar PowerShell en T-SQL usando xp_cmdshell

PowerShell puede utilizarse para crear scripts y automatizar tareas en SQL Server, SharePoint, Window Server, Azure y varias otras tecnologías de Microsoft. PowerShell actualmente está disponible en también en Linux. Se pueden utilizar bucles, operadores para crear scripts potentes para automatizar las tareas con PowerShell.

Por ejemplo, estos cmdlets le permiten realizar copias de seguridad de la fuente c:\sq \ en la carpeta de destino de PowerShell:

copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse

La carpeta de destino tendrá ahora la carpeta de origen con las copias de seguridad:


Figura 9. Copias de seguridad en la carpeta de destino

Si se desea ejecutar el mismo cmdlet de PowerShell con xp_cmdshell en SSMS, se deben ejecutar las siguientes sentencias T-SQL:

xp_cmdshell ‘powershell -command “copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse’

En la sentencia anterior, se está invocando a PowerShell y ejecutando el comando para copiar todos los archivos y carpetas de origen a destino.

El ejemplo mostrado a continuación, demuestra como ejecutar una secuencia de comandos de PowerShell utilizando xp_cmdshell. En primer lugar, crearemos un script llamado script.ps1 con el siguiente contenido:

File_name: script.ps1
copy-item “C:\sql\source” -Destination “C:\sql\destination” –Recurse

Para invocar a un script de PowerShell en xp_cmdshell, utilizaremos las siguientes frases:

xp_cmdshell ‘powershell -command “C:\\sql\\script.ps1″‘
xp_cmdshell and SQL Server PowerShell

A veces es necesario conectarnos a SQL PowerShell (SQLPS). En el siguiente ejemplo se mostrará cómo mostrar la información de la base de datos en un archivo html utilizando xp_cmdshell. El siguiente ejemplo muestra cómo hacerlo.

Primero, cree una secuencia de comandos llamada scriptsql.ps1 con el siguiente contenido:

sqlserver:
cd sql\InstanceName\default\databases
get-childitem | ConvertTo-HTML | Out-File C:\sql\databases.htm

El script abre SQL Server entrando al nombre de instancia (cambiar al nombre de la instancia de SQL Server), predeterminado y bases de datos.

Get-childitem mostrará todas las bases de datos y convert-html out-file convertirá los resultados al formato HTML. El resultado se almacenará en un archivo denominado databases.htm:


Figura 10. Archivo HTML

Cómo conectarse a Azure en SSMS utilizando xp_cmdshell y sqlcmd

En muchas ocasiones es necesario invocar a Azure desde una base de datos local. Normalmente para conectarse a Azure, necesita otra conexión. Xp_cmdshell y sqlcmd son alternativas para crear procedimientos almacenados complejos y scripts que se conectan a bases de datos Azure y locales.

Primero crearemos un servidor SQL en el Portal de Azure. En el portal, vaya a More Services>SQL Servers:


Figura 11. Azure Portal, SQL Servers

Pulse Añadir para agregar un nuevo SQL Server:


Figura 12. Adición de un nuevo SQL Server

Agregue un nuevo SQL Server, un inicio de sesión y una contraseña, cree un nuevo grupo de recursos y seleccione una ubicación y pulse Crear:


Figura 13. Información de SQL Server

Una vez creado, espere unos minutos y actualice la lista de servidores SQL. En este ejemplo, el nombre de SQL Server es sqlshack:


Figura 14. Actualizar Servidores

Para habilitar la máquina local con SSMS, en Portal de Azure, haga clic en SQL Server, vaya a Firewall y presione Add client IP. Esto agregará la máquina local con SSMS. Una vez agregado, pulse Guardar:


Figura 15. Habilitación del acceso a Azure por IP

Seleccione el servidor SQL creado en Azure Portal y seleccione Add client IP. En este sector, se mostrará el nombre de Azure SQL Server:


Figura 16. Nombre de Azure SQL Server

En su SQL Server local, cree un script de sql llamado sqlscript.sql con el siguiente contenido:

CREATE DATABASE SQLSHACKDB

El script, será utilizado para crear una base de datos denominada SQLSHACKDB en Azure.

Las siguientes sentencias T-SQL se utilizarán para ejecutar un script en Azure para crear una base de datos Azure:

En la sentencia de comandos daniel y myPWD son el usuario y la contraseña creados en la Figura 13. Sqlshack.database.windows.net es el nombre del servidor que se muestra en la Figura 16. Estamos invocando a sqlcmd, que es la línea de comandos. Luego nos conectamos a la base de datos master en Azure y recibimos como entrada el script sqlscript.sql.

Si todo marcha bien, en el Portal de Azure, en SQL Databases, podrá ver la base de datos SQLSHACKDB creada:


Figura 17. Base de datos Azure creada con xp_cmdshell.

Conclusión

En este artículo, explicamos cómo copiar datos de una carpeta de origen a otra de destino, también mostramos cómo copiar la ruta completa de archivos a una tabla de SQL. Finalmente, aprendimos cómo ejecutar PowerShell y cómo conectarnos a Azure usando xp_cmdshell.

Referencias

Para obtener más información, consulte estos enlaces:


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional para SQL Server. Es autor experto de SSIS, profesor en IT Academies y tiene más de 13 años de experiencia trabajando con diferentes bases de datos.

Él ha trabajado para el gobierno, compañías de petróleo, sitios web, revistas y universidad alrededor del mundo. Daniel también habla regularmente en conferencias de SQL Server y blogs. Él también es un escritor para material de entrenamiento de SQL Server para exámenes de certificación.

Ver todas las publicaciones de Daniel Calbimonte
Daniel Calbimonte
Comandos SQL

Acerca de Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional para SQL Server. Es autor experto de SSIS, profesor en IT Academies y tiene más de 13 años de experiencia trabajando con diferentes bases de datos. Él ha trabajado para el gobierno, compañías de petróleo, sitios web, revistas y universidad alrededor del mundo. Daniel también habla regularmente en conferencias de SQL Server y blogs. Él también es un escritor para material de entrenamiento de SQL Server para exámenes de certificación. Ver todas las publicaciones de Daniel Calbimonte

614 Views