SQL Server Databases Backup
Based on Olaf Helper‘s work (http://social.technet.microsoft.com/wiki/contents/articles/900.how-to-sql-server-databases-backup-with-powershell-en-us.aspx), here’s the script to back up all non-system databases for SQL Server, with error handling and logging function.
$Error.Clear() $ErrorActionPreference="SilentlyContinue" Clear-Host # Logging $BackUpDate=Get-Date -format yyyy-MM-dd $DirLogs="${pwd}\Logs" $ScriptName=($MyInvocation.MyCommand).Name $ScriptName=$ScriptName -replace ".ps1","" $LogFile="${DirLogs}\${ScriptName}-${BackUpDate}.log" $SkippedDatabases=@("master","model","msdb","tempdb") function LogIt { Param ([string]$strFile,[string]$strText,[bool]$blnTime) if ($blnTime) {Add-Content -Path $strFile -Value ('[' + (Get-Date -Format G) + '] ' + $strText)} else {Add-Content -Path $strFile -Value $strText} } # Check for logs directory. Create if Missing if (!(Test-Path $DirLogs)) {New-Item $DirLogs -Type Directory | Out-Null} # Log Header LogIt $LogFile "" $false LogIt $LogFile "" $false LogIt $LogFile "===================================================================================" $false LogIt $LogFile ("= Script Full Name: `t" + ($MyInvocation.MyCommand).Name) $false LogIt $LogFile ("= Date: `t`t" + (Get-Date -format G)) $false LogIt $LogFile ("= Computer Name: `t" + $env:computername) $false LogIt $LogFile ("= User Name: `t`t" + $env:username) $false LogIt $LogFile "===================================================================================" $false LogIt $LogFile "" $false LogIt $LogFile "===========================================================" $true LogIt $LogFile ("== BackUp of " + $env:computername + " | STARTING") $true LogIt $LogFile "===========================================================" $true LogIt $LogFile "" $false # Full + Log Backup of MS SQL Server databases 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') $SQLInstance="(local)" $SQLBackupDir="${pwd}\SQL" $SQLServer=New-Object Microsoft.SqlServer.Management.Smo.Server $SQLInstance # Check for backup directory. Create if Missing if (!(Test-Path $SQLBackupDir)) {New-Item $SQLBackupDir -Type Directory | Out-Null} $SkippedDBCount=$SkippedDatabases.length $DBCount=($SQLServer.Databases).count $DBTotal=$DBCount - $SkippedDBCount $i=1 # Full-backup for every non-system database ForEach ($db in $SQLServer.Databases) { if ($SkippedDatabases -notcontains $db.Name) { Write-Progress -Activity "Backup SQL databases. Please wait..." -Status "Progress ->" -PercentComplete ($i/$DBTotal*100) -CurrentOperation "Currently processing: $db" $i++ $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($SQLBackupDir + "\" + $db.Name + "_full_" + $timestamp + ".bak", "File") $backup.BackupSetDescription="Full BackUp of " + $db.Name + " " + $timestamp $backup.Incremental=0 # Starting full backup process LogIt $LogFile "Full BackUp of $db started" $true $backup.SqlBackup($SQLServer) LogIt $LogFile "Full BackUp of $db completed" $true # 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($SQLBackupDir + "\" + $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 LogIt $LogFile "Log BackUp of $db started" $true Try { $backup.SqlBackup($SQLServer) LogIt $LogFile "Log BackUp of $db completed" $true } Catch [system.exception] { LogIt $LogFile ("ERROR - " + $_.Exception) $true LogIt $LogFile "" $false Continue } } } } $nbError=$Error.count LogIt $LogFile "===========================================================" $true LogIt $LogFile ("== BackUp of " + $env:computername + " | COMPLETE") $true LogIt $LogFile ("== Error(s): " + $nbError) $true LogIt $LogFile "===========================================================" $true if ($nbError -lt 1) {Write-Host "`n${env:computername}: Script completed successfully!`n" -ForegroundColor DarkGreen} elseif ($nbError -eq 1) {Write-Host "`n${env:computername}: Script completed with 1 error.`n" -ForegroundColor Yellow} else {Write-Host "`n${env:computername}: Script completed with $nbError errors.`n" -ForegroundColor Magenta}