Welcome to Day 21 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
If you don’t have a database backup that you can restore, you’re just one disaster away from being unemployed. Let’s try to prevent that from happening (at least because of not having backups) by performing a full backup of all databases on your server:
#clear variables
$Server = $null
#Assign variables
$Instance = "localhost\SQL2008"
#Assign the SMO class to a variable
$SMO = "Microsoft.SqlServer.Management.Smo"
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
ForEach ($db in $Server.Databases)
{
if ($db.Status -EQ 'Normal' -and $db.IsMirroringEnabled -EQ $false -and `
$db.Name -NE 'tempdb' -and $db.Name -NE 'Verify')
{
$Backup = New-Object ("$SMO.Backup")
$Backup.Action = 'Database' #'Log' for log backups, 'Files' for specific files
$Backup.Incremental = $False # False for full backup, True for Differential
# You can only do full backups of master, so check for this
IF (!($db.Name -eq "master" -and ($Backup.Incremental -eq $True -or $Backup.Action -ne 'Database')))
{
IF ($Backup.Action -EQ 'Log') {$BackupType = 'Log'}
ELSEIF ($Backup.Action -EQ 'Database' -and $Backup.Incremental -EQ $False) {$BackupType = 'Full'}
ELSEIF ($Backup.Action -EQ 'Database' -and $Backup.Incremental -EQ $True) {$BackupType = 'Diff'}
$Backup.BackupSetDescription = "$BackupType Backup of " + $db.Name
$Backup.BackupSetName = $db.Name + ' Backup'
$Backup.Checksum = $true
IF ($Server.EngineEdition -ne "EnterpriseOrDeveloper") {$Backup.CompressionOption = "Off"}
ELSE {$Backup.CompressionOption = "On"}
$Backup.CopyOnly = $False
$Backup.Database = $db.Name
$Backup.MediaDescription = 'Disk'
$dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\" + $BackupType + "\"
IF (!(Test-Path -Path $dir)) {New-Item $dir -Type Directory}
$Backup.Devices.AddDevice($dir + $db.Name + "_" + (Get-Date -format yyyyMMddHHmmss) + '.bak', 'File')
$Backup.SqlBackup($Server) | Out-Null
}
}
}
By changing the Action property, you can take log or file backups; by changing the incremental property you can take a differential backup.
If you are utilizing SMO from SQL Server 2012, a new method has been added: Backup-SqlDatabase. With this, you could perform a full backup of all databases by:
Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Databases | Backup-SqlDatabase
This cmdlet has parameters to control all aspects of the backup.
