Automatizando el backup y restore de una base de datos SQL Server 2014 usando PowerShell

 

Igual que en servidores Linux donde tenemos bases de datos MySql y Oracle automatizamos tareas banales mediante scripts en bash, en los servidores donde tengamos Windows Server podemos apañarnos con Windows Power Shell. Como administradores de las bases de datos Sql server, puede interesar saber algo de scripting en este lenguaje para llegar más lejos que con el Agente de Sql Server y sus trabajos. También es cierto que podemos profundizar todo lo que queramos, empezando con Server Management Objects para interactuar con SQL Server (requiere conocimientos mínimos de .NET y POO) e incluso pudiendo combinarlo con WMI para consultar información relativa al sistema operativo. 

En este ejemplo de script Power Shell (extensión .ps1)  voy a hacer algo sencillo que será refrescar la base de datos del entorno de desarrollo desde una copia que haremos de la base de datos de producción. En mi caso, esto me tiene que permitir elegir si refrescar el entorno de desarrollo o el entorno de test. Lo bueno de PowerShell es que podemos incluir dentro del mismo script .ps1 comandos de la propia linea de comandos de dos.

Antes de empezar abriremos la consola de power shell. Para probar y poder ejecutar scripts desde la consola scripts seguramente hemos de permitirlo ya que por defecto la directiva deshabilita esta opción. Consultamos y modificamos el la restricción con los comandos Get-ExecutionPolicy y Set-ExecutionPolicy:

 

Este script está pensado para ejecutarlo desde el servidor de desarrollo. Para hacerlo más "universal", haré primero una consulta para saber los nombres de la bases de datos accesibles de producción, pidiendo previamente servidor al que conectarse, usuario y clave. Después permito introducir el nombre de la elegida.

*** $SQLServerOrigen = read-host "Nombre del servidor origen" $SQLUserOrigen = read-host "Usuario bbdd origen" $SQLClaveUserOrigen = read-host "Clave" $SqlQuery = "select name from master.dbo.sysdatabases order by 1 asc" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServerOrigen; User id=$SQLUserOrigen; Password=$SQLClaveUserOrigen" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() clear $DataSet.Tables[0] echo "" $BBddOrigen = read-host "Base de datos de $SQLServerOrigen a copiar" ***

Para hacer la copia, en esta primera versión uso el cliente sql de la linea de comandos (sqlcmd) y un sql a pelo montado de forma dinámica para hacer el backup de solo copia en una ruta fijada. Montaré una unidad de red temporal para hacer la copia, se copia, se desmonta la unidad y se lanza de nuevo desde sqlcmd el restore (mapeando y hardcodeando la nueva ruta por la distinta ubicación de los .mdf y .ldf). Finalmente elimino el fichero.

*** sqlcmd -S $SQLServerOrigen -U $SQLUserOrigen -P $SQLClaveUserOrigen -Q "BACKUP DATABASE [$BBddOrigen] TO DISK = N'F:\Backup\$BBddOrigen\$BBddOrigen.temp.bak' WITH COPY_ONLY" echo "\\$SQLServerOrigen\F$\Backup\$BBddOrigen\" Net Use T: \\$SQLServerOrigen\F$\Backup\$BBddOrigen Copy-Item T:\$BBddOrigen.temp.bak C:\$BBddOrigen.temp.bak Remove-Item T:\$BBddOrigen.temp.bak Net Use T: /delete $BBddDestino = read-host "Base de datos destino" SQLCMD -E -S localhost -Q "RESTORE DATABASE $BBddDestino FROM DISK='C:\$BBddOrigen.temp.bak' WITH FILE=1, MOVE N'$BBddOrigen' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$BBddDestino.mdf', MOVE N'${BBddOrigen}_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$BBddDestino.ldf' " #En la cadena de texto anterior, BBDDOrigen va entre {} para poder escapar el '_' acompañado del nombre de la variable y no interprete el _log como parte del nombre de la misma Remove-Item C:\$BBddOrigen.temp.bak ***

En el script anterior, en lugar de usar sqlcmd para hacer el backup, podríamos haberlo hecho usando SMO (Server Management Objects). En este caso, el equivalente al sqlcmd sería algo parecido a lo siguiente:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") #Preparamos el servidor y cargamos tb el directorio de backup $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "($SQLServerOrigen)" $backupDirectory = $server.Settings.BackupDirectory $db = $server.Databases[$dbToBackup] $dbName = $db.Name $timestamp = Get-Date -format yyyyMMddHHmmss $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") $smoBackup.Action = "Database" $smoBackup.BackupSetDescription = "Full Backup of " + $dbName $smoBackup.BackupSetName = $dbName + " Backup" $smoBackup.Database = $dbName $smoBackup.MediaDescription = "Disk" $smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File") $smoBackup.SqlBackup($server) #Obtenenmos la lista de ficheros en el directorio de backup para luego filtrar y mostrar los .bak $directory = Get-ChildItem $backupDirectory $backupFilesList = $directory | where {$_.extension -eq ".bak"} $backupFilesList | Format-Table Name, LastWriteTime

La gracia de usar los SMO es la complejidad. Seguramente segun el objeto podremos profundizar algo más y modificar parámetros y obtener información que no conseguiriamos de otra manera. Este trozo de código de arriba, a diferencia del sqlcmd está usando la ruta definida por defecto para los backups del propio sqlserver en lugar de hardcodearla. Ciertamete no es un avance pero ya vemos que la interacción es mejor. Si seguimos con SMO, el equivalente al restore con sqlcmd donde se permite elegir el nombre de la base de datos destino y se renombran los ficheros. Sería algo parecido a lo siguiente:

#Librerias... [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null $backupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.bak' $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)" $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File") $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore") #Propiedades de la restauracion $smoRestore.NoRecovery = $false; $smoRestore.ReplaceDatabase = $true; $smoRestore.Action = "Database" $smoRestorePercentCompleteNotification = 10; $smoRestore.Devices.Add($backupDevice) #Nombre de la nueva bbdd $BBddDestino = read-host "Base de datos destino" $smoRestore.Database =$BBddDestino #Nuevos ficheros... $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreFile.LogicalFileName = $BBddDestino $smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $BBddDestino + "_Data.mdf" $smoRestoreLog.LogicalFileName = $BBddDestino + "_Log" $smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $BBddDestino + "_Log.ldf" $smoRestore.RelocateFiles.Add($smoRestoreFile) $smoRestore.RelocateFiles.Add($smoRestoreLog) #Restauracion $smoRestore.SqlRestore($server)

 

 

En conclusión...

...invirtiendo un poco de tiempo la ganancia futura puede ser mayor. Podemos ahorrar mucho tiempo en tareas repetitivas o hacer verdaderas obras de arte que permitan hacer cosas más viriles. Por ejemplo algo como desactivar el reflejo del mirror en una base de datos reflejada para tenerla preparada en pocos segundos. Recrear el mismo reflejo de forma automática (o a un solo intro). Podemos reinventar la rueda y monitorizar nosotros determinados aspectos de nuestro propio servidor y tener nuestra propia versión de los hechos (sin contar la del departamento de sistemas...).