Respaldo de base de datos con PowerShell: Como respaldar base de dato SQL Server sin agente
Respaldo de base de datos con PowerShell
En algún momento del día, un compañero pregunto como respaldar base de datos, sin el agente del SQL, ya que no tiene sino SQL Server Express, entonces me tome la tarea de escribir un scritp para generar un respaldo de todas las base de datos, y posteriormente enviarlas por ftp, notificando por correo dicha actividad
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
Try { $user = 'sa' $pass = '1234' $serverName = 'MYPC\SQLISTO' $db = 'tempdb' $backupDirectory = "E:\PSScripts\backups\" $daysToStoreBackups ="7" $ftp = "ftp://SQL-LISTO.com/" $user = "MiUsuario" $pass = "HolaMundo" $From 'mail@origen.com' $To 'mail@destino.com' $SubjectError 'Asunto del correo' $SubjectSuccess 'Asunto del correo' $Body 'Cuerpo del correo' $SmtpServer 'IP_SMTP_SERVER' $Port 'Puerto_SMTP_Server' [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName $dbs = $server.Databases foreach ($database in $dbs | where { $_.IsSystemObject -eq $False }) { $dbName = $database.Name $timestamp = Get-Date -format yyyy-MM-dd-HHmmss $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak" $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($targetPath, "File") $smoBackup.SqlBackup($server) "backed up $dbName ($serverName) to $targetPath" } Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force } "removed all previous backups older than $daysToStoreBackups days" $webclient = New-Object System.Net.WebClient $webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) foreach($item in (dir $backupDirectory "*.bak")){ "Uploading $item..." $uri = New-Object System.Uri($ftp+$item.Name) $webclient.UploadFile($uri, $item.FullName) } } Catch { Send-MailMessage -From $From -To $To -Subject $SubjectError -Body $Body -SmtpServer $SmtpServer -Port $Port } Finally { Send-MailMessage -From $From -To $To -Subject $SubjectSuccess -Body $Body -SmtpServer $SmtpServer -Port $Port } |
