Script de Powershell para MSSQL

Este script puede utilizarse para importar automáticamente archivos SQL con más de 20 minutos de antigüedad en la base de datos MSSQL. Es útil si activas el registro de texto SQL para configuraciones de alto rendimiento. Para ejecutar este script, debes guardarlo con la extensión .ps1, y habilitar la ejecución de scripts de PowerShell en Windows mediante el editor de directivas de grupo (o debes ejecutar el script como Administrador).

Crea la siguiente tabla en MSSQL

CREATE TABLE messagereport
(
    logid int IDENTITY(1,1),
    messageid varchar(64),
    fromconnection varchar(64),
    fromaddress varchar(64),
    requestedconnection varchar(64),
    toconnection varchar(64),
    toaddress varchar(64),
    messagetext varchar(1024),
    route varchar(64),
    datecreated datetime,
    datedeliverytoconnectionsuccess datetime,
    datedeliverytoconnectionfailed datetime,
    deliverytoconnectionreference varchar(256),
    deliverytoconnectionstatusmessage varchar(1024),
    datedeliverytorecipientsuccess datetime,
    datedeliverytorecipientfailed datetime,
    deliverytorecipientstatusmessage varchar(1024),
    status varchar(64)   
)

El primer paso es crear la tabla messagereport en la base de datos. Todos los mensajes entrantes y salientes se almacenarán en esta tabla y se actualizarán según su estado. Si ya has creado esta tabla, puedes omitir este paso. Copia el script de creación de tabla anterior y ejecútalo en tu base de datos como se muestra en la Figura 1.

Guarda el siguiente script de PowerShell como "dbimport.ps1"

El siguiente paso es crear el archivo "dbimport.ps1" en la carpeta C:\Ozeki como se muestra en la Figura 2. Este archivo contendrá el script que ejecuta consultas SQL periódicamente en la base de datos.

$ProcessedDir = "C:\Ozeki\Processed"
New-Item -ItemType Directory -Force -Path $ProcessedDir

#bucle infinito
while (1) {
	#listar archivos con más de 20 minutos de antigüedad
	#asegúrate de que el tiempo sea mayor que el período configurado en el informe
	Get-ChildItem "C:\Ozeki\Reporting_SQL" -Filter *.sql | 
		Where{$_.LastWriteTime -le (Get-Date).AddMinutes(-20)} |
	
	Foreach-Object {
		#cargar el contenido del archivo en la base de datos MSSQL
		Write-Output $_.FullName
		$Text = Get-Content -Path $_.FullName
		Invoke-Sqlcmd -ServerInstance MyComputer\MainInstance -Database MyDatabase
		-Query "$Text" -Username "MyUser" -Password "MyPass"

		
		#mover el archivo procesado al directorio de procesados
		Move-Item -Path $_.FullName -Destination $ProcessedDir
	}
	
	#esperar 5 segundos
	Start-Sleep -s 5
}

Copia el script de PowerShell anterior y pégalo en el archivo "dbimport.ps1" como se muestra en la Figura 3.

Finalmente, modifica el script según tu propia base de datos MSSQL. En la instancia del servidor, define el nombre del equipo y la instancia de la base de datos MSSQL. Ingresa el nombre de usuario y la contraseña que deseas usar, y reemplaza "Mydatabase" con el nombre de tu base de datos (Figura 4).

Ejecuta el script como administrador

powershell -ExecutionPolicy Bypass -File dbimport.ps1

Ahora ejecuta el script en PowerShell ejecutando el comando anterior como se muestra en la Figura 5.

Puedes ver que el script ejecuta todos los archivos SQL con más de 20 minutos de antigüedad en la carpeta 'C:\Ozeki\Reporting_SQL' uno por uno (Figura 6).

Cada archivo SQL que el script ha ejecutado se mueve a la carpeta Processed para que no se pierda ninguna consulta (Figura 7). Desde aquí, puedes eliminarlos manualmente si ya no los necesitas.

Finalmente, si revisas la base de datos, puedes ver que todos los mensajes han sido insertados en la tabla messagereport como muestra la Figura 8.