Data Crazy World

SQL Server, PowerShell, Power BI, Power Platform,... Los datos, ese mundo tan interesante, variado, divertido y ... ¡Loco!

Automatiza backups de SQL Server con PowerShell

por · 16 de marzo de 2025

Hace unas semanas salió en el canal de Telegram SQL Server Español el tema de los restores automáticos, y yo comenté que tenía por ahí unos scripts para automatizarlos. El tema es bastante extenso y puede tener su complicación, además de que no estaría completo del RESTORE sin el de BACKUP, por lo que he pensado preparar varios artículos sobre esto. Empecemos por lo primero que necesitamos, que es generar el Backup. ¡Allá vamos!

Lo primero que quiero indicar es que para este tipo de automatizaciones yo elijo Power Shell. Puede haber mil formas de hacerlo e igual alguna es mejor que lo que yo planteo, pero como siempre digo, es la forma que yo encontré de solucionar mi problema.

También quisiera aclarar que este script parte de uno que encontré por internet hace muchos años y que fui adaptando. Me encantaría poneros el origen de éste, pero es que actualmente lo desconozco. Así que quiero agradecer a esa persona desconocida para mi que puso su primer granito para que este script naciera.

El script que voy a explicar a continuación lanza una serie de hilos que se encargan de generar el backup de las bbdds indicadas, empezando por las más grandes, y luego mover ese archivo .bak a una carpeta.

Elementos por configurar:

  • IP del servidor de SQL server: $SQLServer
  • Nombre de la instancia de SQL server: $SQLServerName
  • Usuario para lanzar los comandos de este script (debe tener los permisos suficientes): $dbuser
  • Password del usuario indicado anteriormente: $dbpass
  • Bases de datos que quieras excluir de este script: por ejemplo, tempdb: $objetcExclude
  • Url del fichero del log de lo que hace y no hace el script: $logfile
  • Listado de tareas/pasos que quieres que se hagan con cada una de las bases de datos que se vean incluidas en la ejecución del script: $Mastertasks
  • Nº máximo de hilos que quieres que se lancen a la vez: $maxThreads
  • Nº de intentos máximos que se hará de cada tarea en caso de error: $NumIntentosMax
  • El criterio que haremos de ordenación de las bbdds: $objectSortExpression1
  • Cada cuantos milisegundos comprobaremos si la tarea ha terminado: $SleepTimer
  • La cantidad máxima de milisegundos que vamos a dejar en una tarea. Pasado este tiempo mataremos la tarea. Si no queremos limitarla, indicaremos 0: $querytimeout
  • Nº de segundos de máxima duración de este script al completo. Pasado el cuál, mataremos todas las tareas abiertas por él. $MaxResultTime

Además, es importante indicar que este script tiene que ejecutarse bajo una configuración de entorno de 64 bits.

Hasta aquí tengo lo siguiente:

###############################################################################################
# Datos a rellenar
# $SQLServer --> Ip del servidor
# $SQLServerName --> Nombre del servidor
# $objectExclude --> BDs que queramos excluir
# $Logfile --> Url del fichero de log
# $Mastertasks --> Dentro poner las urls en la Task2 de dónde hacer el backup y en la Task4 de dónde copiar a donde copiarlo.
###############################################################################################
[Environment]::Is64BitProcess
$SQLServer = "0.0.0.0"
$SQLServerName = "NombreInstancia"
$db3 = "master" # No lo he indicado porque no hay que configurarla, pero aquí hay que indicar el nombre de la bbdd master
$dbuser = "NombreUsuario"
$dbpass = "PassUsuario"
$maxThreads = 4
$querytimeout = 0 #0 significa que no hay límite. Si necesitas se puede cambiar.
$objectExclude = 'tempdb' -split ", " #bases de datos que no queremos hacer el backup
$objectNameField1 = "DATABASE_NAME" ##esto es un valor que obtenemos del $qcd
$objectNameField2 = "DATABASE_SIZE" ## esto es un valor que obtenemos del $qcd
$objectSortExpression1 = @{Expression={$_[1]}; Descending=$true} # En el campo DATABASE_SIZE devuelto por sp_databases, para ordenar por tamaño
$SleepTimer = 1000 #después de X millisegundos, comprueba si los jobs ha terminado. 
$MaxResultTime = 14400 # después X segundos, se matan todos los jobs. 7200 son dos horas.
$timestamp = $(get-date -f yyyyMMddhhmmss) #para coger la marca de tiempo que usaré luego en el $Logfile
$Logfile = "C:\DirectorioBackupLogFiles\"+$SQLServerName+"_"+$timestamp+".log" #Url del Directorio donde dejar los logs
$NumIntentosMax = 6 #Nº intentos

Ahora voy a definir el comando sql que va a devolver las bases de datos y su tamaño. En este caso, como yo trabajaba con una instancia en Always On, me aseguro de que no estoy cogiendo BBDDs de lectura. También me quedo sólo con las que estén ONLINE y de las que tengo permiso. De todo ello se encarga el comando indicado en $qscd que tiene que devolver dos campos: DATABASE_NAME y DATABASE_SIZE indicados anteriormente en las variables $objectNameField1 y $objectNameField2.

$qcd = "SELECT
	        b.DATABASE_NAME
	        ,b.DATABASE_SIZE
        FROM
	        (
		        SELECT name as DATABASE_NAME FROM sys.databases where is_read_only = 0
		        EXCEPT
		        SELECT database_name FROM sys.availability_databases_cluster
		        UNION
		        select
				        d.database_name
			        from sys.dm_hadr_availability_replica_states ars
			        inner join sys.availability_groups ag
			        on ars.group_id = ag.group_id
			        inner join sys.availability_databases_cluster d
				        on ars.group_id = d.group_id
		        where ars.is_local = 1
				        and ars.role_desc='PRIMARY'
	        )a
	        inner join
	        (
		        select
			        DATABASE_NAME   = db_name(s_mf.database_id),
			        DATABASE_SIZE   = sum(convert(bigint,s_mf.size))
		        from
			        sys.master_files s_mf
		        where
			        s_mf.state = 0 and -- ONLINE
			        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
		        group by s_mf.database_id
	        )b
	        on a.DATABASE_NAME = b.DATABASE_NAME"

Lo siguiente que necesito hacer es crear la función que se va a encargar de escribir el Log.

Write-Host "Paso 0:Crear LogWrite"
Function LogWrite
{
   Param ([string]$logstring)

   $timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }

   $LogEntry = $timestamp +": "+$logstring

   Add-content $Logfile -value $LogEntry
}
If ([Environment]::Is64BitProcess) {LogWrite "Version64bits:true"}else{LogWrite "Version64bits:false"}

A continuación voy a recoger el listado de bases de datos (usando la sentencia indicada en $qcd ), excluyendo las que indicamos en $objectExclude y ordenarlas como se indicaba en $objectSortExpression1.

Con todo ello creamos un hashtable vacío que convertimos en un diccionario ordenado, para incluir luego una a una las bases de datos, con las características indicadas en los campos $objectNameField1 y $objectNameField2, y que se vayan ordenando.

Write-Host "Paso 1: Obtener la lista de BBDD y ordenar"
LogWrite " Paso 1: Obtener la lista de BBDD y ordenar "

# Obtener la lista de BBDD y ordenar
$objects = @((Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -querytimeout $queryTimeout -Username $dbuser -Password $dbpass) | where {$objectExclude -notcontains $_.$objectNameField1} | sort $objectSortExpression1)

#Empezamos a tratar los objetos
$databases = [ordered]@{}
$databasesTasks =[ordered]@{}#name,Tasks
for ($i=0; $i -lt $objects.length; $i++) {
	$object = $objects[$i].$objectNameField1
    $size = $objects[$i].$objectNameField2
    
    $databases.Add($object,$size)   
}

Bien, ahora es el momento de definir cada tarea que quiero que hagan cada uno de las BBDDs. Aquí defino el PATRÓN y luego este se aplicará en cada una de ellas. En mi caso voy a definir lo siguiente:

  1. Indica en el Log que va a empezar con la BBDD XXX
  2. Lanza el comando de Backup. Aquí tendrás que indicar la url donde quieres dejar el .bak
  3. Indica en el Log que ha terminado de hacer el backup de la BBDD XXX
  4. Copia el archivo .bak a una carpeta que tendrás que indicar

En mi caso hago este paso 4 porque quiero que el backup acabe en una carpeta compartida con otro servidor donde voy a hacer el restore, pero no es necesario. Lo he querido dejar, para mostrar que se pueden encadenar más pasos y no sólo el backup.

Por último, indicaré que tiene que empezar por la primera tarea, la 1.

$Mastertasks = @(# taskOrder, taskName, scriptToRun) args0=databaseName args1=$SQLServer args2=$db3 args3=$dbuser args4=$dbpass args5=$querytimeout args6= $SQLServerName. Only one command.
	,@(1, 'Log-message1', 'Write-Host "$(Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }) : Starts backup with $args0"')
	,@(2, 'backup', 'Invoke-Sqlcmd -ServerInstance $args1 -Database $args2 -Username $args3 -Password ''$args4'' -Query "BACKUP DATABASE [$args0] TO DISK=N''B:\NombreCarpetaBackups\$args6_$args0.bak'' WITH INIT, CHECKSUM" -querytimeout $args5') ##Configurar URL
    ,@(3, 'Log-message2', 'Write-Host "$(Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }) : Ends backup with $args0"')
    ,@(4, 'Move-files','Copy-Item -Path "B:\NombreCarpetaBackups\$args6_$args0.bak" -Destination "\\11.11.11.11\carpetas_compartida\$args6_$args0.bak"')
) ##Configurar URL y carpeta compartida

$startAtTask = 1 #i.e. Si la red no estaba disponible, es posible que necesites volver a ejecutar comenzando desde esa tarea.

Como he dicho esto es el PATRÓN. Ahora hay que aplicarlo a cada una de las BBDDs de mi lista.

#Por cada BBDD crearemos una lista de Tasks individual, partiendo de la $MasterTasks
$MaxTasks = 0
foreach ($h in $databases.Keys) {  
    $tasks = @()  # taskOrder, taskName, scriptToRun) args0=databaseName args1=$SQLServer args2=$db3 args3=$dbuser args4=$dbpass args5=$querytimeout args6= $cmd. Only one command.
    $task0 =  "" | select taskOrder, taskName,  scriptToRun
    $task0.taskOrder = $Mastertasks[0][0];
    $task0.taskName = $Mastertasks[0][1]
    $task0.scriptToRun = $Mastertasks[0][2].Replace('$args0',$h)
    $tasks += $task0
         
    $task1 =  "" | select taskOrder, taskName,  scriptToRun
    $task1.taskOrder = $Mastertasks[1][0];
    $task1.taskName = $Mastertasks[1][1]
    $task1.scriptToRun = $Mastertasks[1][2].Replace('$args0',$h).Replace('$args1',$SQLServer).Replace('$args2',$db3).Replace('$args3',$dbuser).Replace('$args4',$dbpass).Replace('$args5',$querytimeout).Replace('$args6',$SQLServerName)
    $tasks += $task1

    $task2 =  "" | select taskOrder, taskName,  scriptToRun
    $task2.taskOrder = $Mastertasks[2][0];
    $task2.taskName = $Mastertasks[2][1]
    $task2.scriptToRun = $Mastertasks[2][2].Replace('$args0',$h)
    $tasks += $task2

    $task3 =  "" | select taskOrder, taskName,  scriptToRun
    $task3.taskOrder = $Mastertasks[3][0];
    $task3.taskName = $Mastertasks[3][1]
    $task3.scriptToRun = $Mastertasks[3][2].Replace('$args0',$h).Replace('$args6',$SQLServerName)
    $tasks += $task3
        
    $databasesTasks.Add($h,($tasks | sort taskOrder))
    if ($MaxTasks -lt $tasks.Count) { $MaxTasks = $tasks.Count}
}

Ahora configuraré el entorno, para indicar cosas como el nº de tareas máxima, el nº de hilos,… Esto es necesario porque lo voy a lanzar en paralelo usando threads y necesito configurarlos.

Write-Host "Paso 2: Configuración del entorno"
LogWrite " Paso 2: Configuración del entorno "

#environment setup
$RunspacePools = @()
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$taskInfo_db = @{} #key=databasename; values = $taskInfo
$maxTaskOrder = -1
$objectInfo_db = @{} #key=databasename; values = $objectInfo
$objectInfoArr = @(0) * $databasesTasks.count
$output = ""
$errors = ""
$errorCount_db = @{} #key=databasename; values = $errorCount
#Hacemos un RunSpacePool por cada tarea 
for ($i=0; $i -lt $MaxTasks; $i++) {
    $RunspacePools += [runspacefactory]::CreateRunspacePool(1, $maxThreads, $ISS, $Host)
    $RunspacePools[$i].Open()
}
foreach ($h in $databasesTasks.Keys) { 
    $taskInfo = @{} # key=taskOrder; value=nextTaskOrder
    $objectInfo = @{} # key=taskId; values=each database
    $errorCount = @{}
    $tasks = $databasesTasks[$h]
    for ($i=0; $i -lt $tasks.length; $i++) {
	    if ($taskInfo.Count -eq 0 -Or -Not $taskInfo.ContainsKey($tasks[$i].taskOrder)) { #taskOrder
		    $taskInfo.Add($tasks[$i].taskOrder, -1)
		    if ($tasks[$i].taskOrder -gt $maxTaskOrder) { $maxTaskOrder = $tasks[$i].taskOrder }
	    }
	    $objectInfo.Add($i, $objectInfoArr.clone())
	    $tasks[$i].scriptToRun = '$ErrorActionPreference = "Stop"; try { $output = ' + $tasks[$i].scriptToRun + ' 2>&1 } catch { $err = $_.Exception; $errors = $err.Message; while($err.InnerException) { $err = $err.InnerException; $errors += "|" + $err.Message } } $LastExitCode; $errors | where { $_ } | Out-String; $output | where { $_ } | Out-String'
        $errorCount.Add($i, 0)
    }
    foreach ($key in @($taskInfo.Keys)) { $taskInfo[$key] = ($taskInfo.Keys | where {$_ -gt $key} | sort | select -First 1) }
    $taskInfo_db.Add($h,$taskInfo)
    $objectInfo_db.Add($h,$objectInfo)
    $errorCount_db.Add($h,$errorCount)

Ahora crearé la función que va a crear cada Thread y voy a decirle qué tiene que hacer, que viene a ser lanzar en la bbdd que se le indica, las tareas indicadas, con nº máximo de intentos.

Write-Host " Paso 3: función para crear el hilo y comenzar el procesamiento. "
LogWrite " Paso 3: función para crear el hilo y comenzar el procesamiento. "

#: función para crear el hilo y comenzar el procesamiento
function CreateThread() {
	param ([string]$databaseName, [int]$objectIndex, [int]$taskIndex, [int]$taskOrder, [ref]$Jobs), [int]$NumIntentosMax       
    
    $PowershellThread = [powershell]::Create().AddScript(
        $databasesTasks[$databaseName][$taskIndex].scriptToRun
        ) 	
	$PowershellThread.RunspacePool = $RunspacePools[$taskIndex]
	$Handle = $PowershellThread.BeginInvoke()
    LogWrite " IniciarInvocación BBDD: $databaseName. TaskIndex: $taskIndex. TaskOrder: $taskOrder"
	$Job = "" | select Handle, Thread, DatabaseName, ObjectIndex, TaskIndex, TaskOrder, NumIntentosMax
	$Job.Handle = $Handle; 
    $Job.Thread = $PowershellThread;
	$Job.DatabaseName = $databaseName; 
    $Job.ObjectIndex = $objectIndex; 
    $Job.TaskIndex = $taskIndex; 
    $Job.TaskOrder = $taskOrder;
	$Job.NumIntentosMax =$NumIntentosMax
	$Jobs.value += $Job;
}
$ResultTimer = Get-Date

Configurado ya el entorno y creada la función para crear los threads, sólo nos queda empezar a lanzarlo todo.

#Paso 4: comenzar a procesar la primera tarea para cada base de datos.
#Recorro las BBDDs y llamo al CreateThread
$i=0
foreach ($h in $databasesTasks.Keys) {    
    $tasks = $databasesTasks[$h]
    $tasks | where {$_.TaskOrder -eq $startAtTask} | foreach { CreateThread $h $i ([array]::IndexOf($tasks, $_)) $_.TaskOrder ([ref]$Jobs) $NumIntentosMax  }
    $i++
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
	#Actualizar trabajos completados y liberarlos.
	foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
		#actualiza estadp
		$objectInfo_db[$Job.DatabaseName][$Job.TaskIndex][$Job.ObjectIndex] = 1
		#Cojo resultados
        $databaseName = $Job.DatabaseName
        $taskOrder = $Job.TaskOrder
        $taskIndex = $Job.TaskIndex
		$results = $Job.Thread.EndInvoke($Job.Handle)
        LogWrite "EndInvoke DB: $databaseName; TaskIndex: $taskIndex; TaskOrder: $taskOrder; Results: $results"
		if (($results[0] -and $results[0] -ne 0) -or $results[1] -ne "") 
		{  #Si hay error.
			If ($errorCount_db[$Job.DatabaseName][$Job.TaskIndex] -lt $Job.NumIntentosMax)
			{
				#Creo otro Thread
				LogWrite "Retry DB: $databaseName; TaskIndex: $taskIndex; TaskOrder: $taskOrder;"
				CreateThread $Job.DatabaseName $Job.ObjectIndex $Job.TaskIndex $Job.TaskOrder ([ref]$Jobs) $Job.NumIntentosMax
			}
			else
			{
				$errors += $results[1] + "`r`n"
				$output += $results[2] + "`r`n"
				$objectInfoArr[$Job.ObjectIndex] = 1; 
			}
			$errorCount_db[$Job.DatabaseName][$Job.TaskIndex] += 1; 
		}
		if ($errorCount_db[$Job.DatabaseName][$Job.TaskIndex] -eq 0 -or $errorCount_db[$Job.DatabaseName][$Job.TaskIndex] -gt 3)
		{
			#lanzo la siguiente tarea
			if ($Job.TaskOrder -lt $maxTaskOrder -and #Hay tareas pendientes
				(@($Jobs | where {$_.TaskOrder -eq $Job.TaskOrder -and #mismo taskOrder
									 $_.DatabaseName -eq $Job.DatabaseName -and #mismo database
									 $_.Handle.IsCompleted -eq $False}).count -eq 0) -and #no hay thread activo
				$objectInfoArr[$Job.ObjectIndex] -eq 0) { #No hay errores hasta ahora.
				$tasks | where {$_.TaskOrder -eq $taskInfo[$Job.TaskOrder]} | foreach { CreateThread $Job.DatabaseName $Job.ObjectIndex ([array]::IndexOf($tasks, $_)) $_.TaskOrder ([ref]$Jobs) $Job.NumIntentosMax}
			}
		}
		#end thread
		$Job.Thread.Dispose()
		$Job.Thread = $Null
		$Job.Handle = $Null		
	}
	$currentTime = Get-Date
	if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
		Write-Error "Parece que el script secundario está congelado, intenta aumentar MaxResultTime."
        LogWrite "¡¡ERROR!!! Parece que el script secundario está congelado, intenta aumentar MaxResultTime."
		break
	}
	#Espera
	Start-Sleep -Milliseconds $SleepTimer
}

Por último, queda asegurarse que se cierran todos los threads

Write-Host "Paso 5: liberar los grupos de hilos."
LogWrite "Paso 5: liberar los grupos de hilos."
#liberamos los grupos de hilos
foreach ($r in $RunspacePools.Keys) {   
	$RunspacePools[$r].Close() | Out-Null
	$RunspacePools[$r].Dispose() | Out-Null
}
if (($errors -replace "`r`n", "") -ne "") { 
	LogWrite "ERROR!!! $errors"
	throw $errors + " " + $output 
} else { 
	$output 
}

Bueno, es un script complejo que realmente sirve para automatizar el lanzamiento de comandos en una serie de hilos en paralelo, y que yo he utilizado para generar mis backups de manera automática, iniciando siempre por los más grandes.

Una vez juntéis todos los trozos y generéis vuestro archivo .ps1, basta con que uséis el programador de tareas de Windows para tenerlo automatizado. Si no queréis este último paso, siempre podéis ejecutarlo vosotros lanzándolo manualmente.

¡Espero que os sirva de ayuda! 😉

Etiquetas:

También te podría gustar...

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.