Welcome to Day 21 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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:
|
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 |
#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:
|
1 |
Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Databases | Backup-SqlDatabase |
This cmdlet has parameters to control all aspects of the backup.
