SQL Server
http://www.dataprix.com/blog-it/bases-datos/sql14-automatizando-backup-restore-base-datos-usando-powershell
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...).
SQL Server
https://mastergt68.wordpress.com/2012/07/
Copias de respaldo de SQL Server 2008 R2 con PowerShell
Hace poco, con mi buen amigo Andrés Rojas, por cierto, líder de la naciente comunidad ShareCol, SharePoint Colombia, necesitábamos una serie de copias de respaldo de algunas bases de datos de SharePoint Server 2010 SP1 almacenadas en un servidor SQL Server 2008 R2 SP1.
Las bases de datos eran un poco más de 20, así que decidí usar PowerShell para atender el requerimiento de Andrés. Procedí entonces a abrir el PowerShell ISE y a escribir la serie de comandos de scripting de PowerShell para lograr la tarea. Umm, pero primero me di cuenta que no tenía acceso al PowerShell ISE en el servidor SQL Server…
Bueno, a habilitarlo en las características del servidor. Sale así:
Se activa la casilla de verificación, se sigue el asistente y listo. Cuando se busca en el menú y se ejecuta, se ve así:
Yo reconozco que apenas hasta ahora estoy ingresando en el maravilloso mundo de PowerShell y pues frente a esto y porque había que resolver rápidamente el asunto con Andrés, visité el Wiki de TechNet y me encontré con este artículo: How To: SQL Server Databases Backup with PowerShell (en-US). Así que usé el popular “copiar & pegar” y en la ventana pegué el código que me sugería la página. Así se veía:
## Full + Log Backup of MS SQL Server databases/span>
## with SMO.
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo');
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc');
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');
# Required for SQL Server 2008 (SMO 10.0).
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');
$Server = "(local)"; # SQL Server Instance.
$Dest = "D:\Backup\"; # Backup path on server (optional).
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server;
# If missing set default backup directory.
If ($Dest -eq "")
{ $Dest = $server.Settings.BackupDirectory + "\" };
Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
# Full-backup for every database
foreach ($db in $srv.Databases)
{
If($db.Name -ne "tempdb") # Non need to backup TempDB
{
$timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;
$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");
$backup.Action = "Database";
$backup.Database = $db.Name;
$backup.Devices.AddDevice($Dest + $db.Name + "_full_" + $timestamp + ".bak", "File");
$backup.BackupSetDescription = "Full backup of " + $db.Name + " " + $timestamp;
$backup.Incremental = 0;
# Starting full backup process.
$backup.SqlBackup($srv);
# For db with recovery mode <> simple: Log backup.
If ($db.RecoveryModel -ne 3)
{
$timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;
$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");
$backup.Action = "Log";
$backup.Database = $db.Name;
$backup.Devices.AddDevice($Dest + $db.Name + "_log_" + $timestamp + ".trn", "File");
$backup.BackupSetDescription = "Log backup of " + $db.Name + " " + $timestamp;
#Specify that the log must be truncated after the backup is complete.
$backup.LogTruncation = "Truncate";
# Starting log backup process
$backup.SqlBackup($srv);
};
};
};
Write-Output ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
El PowerShell ISE o ambiente integrado de scripting, permite realizar varias cosas, entre ellas ejecutar el .PS por supuesto y mientras empiezo a estudiar y a validar algunas cosas que se pueden hacer con PowerShell y con los SQL Server Management Objects, pues los dejo con este pequeño snippet que me ayudó a hacerle las copias de respaldo a Andrés.
Así que con este post empieza mi estudio formal en PowerShell para tareas prácticas en SQL Server. Cualquier aspecto interesante o de valor, se los compartiré por este medio.
