Backup-OLADatabase.ps1
|
<#PSScriptInfo
.VERSION 1.0 .GUID e690e558-357e-43fb-9bcf-5c39b33ce527 .AUTHOR Rob Sewell .DESCRIPTION This function will perform an ad-hoc backup using OLA Hallengrens solution - It doesnot expose all of the capabilities but is useful for quick backups when needed Note the Database paramter is dynamically filled once you type it .COMPANYNAME .COPYRIGHT .TAGS SQL, Ola Hallengren, Backup .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .Synopsis To perform an ad-hoc backup using OLA Hallengrens solution .DESCRIPTION This function will perform an ad-hoc backup using OLA Hallengrens solution - It doesnot expose all of the capabilities but is useful for quick backups when needed Note the Database paramter is dynamically filled once you type it .EXAMPLE Backup-OlaDatabase -instance SERVERNAME -Type LOG -Share \\backupshare -Database Database This will perform a LOG backup of the Database database on the SERVERNAME instance to the \\backupshare Share. Note the Database paramter is dynamically filled once you type it .EXAMPLE Backup-OlaDatabase -instance SERVERNAME -Type FULL -Share X:\backups -Database Database This will perform a FULL backup of the Database database on the SERVERNAME instance to the X:\backups Share. Note the Database paramter is dynamically filled once you type it .EXAMPLE Backup-OlaDatabase -instance SERVERNAME -Type DIFF -Share \\TheShareForBackups -Database Database -OutputResults This will perform a DIFF backup of the Database database on the SERVERNAME instance to the \\TheShareForBackups Share and output the message to the screen. Note the Database paramter is dynamically filled once you type it .NOTES IF YOU HAVE THE STORED PROCEDURE IN A DIFFERENT DATABASE - You will need to alter line 130 AUTHOR - Rob Sewell https://sqldbawithabeard.com DATE - 27/10/2016 #> function Backup-OlaDatabase { [CmdletBinding( SupportsShouldProcess=$true, PositionalBinding=$false, ConfirmImpact='Medium')] [Alias()] Param ( # The Server/instance [Parameter(Mandatory=$true,HelpMessage='This is the Instance that the database is on', ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, ValueFromRemainingArguments=$false, Position=0)] [ValidateNotNull()] [ValidateNotNullOrEmpty()] [string]$Instance, # The Server/instance [Parameter(Mandatory=$true,HelpMessage='This is the type of backup', ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, ValueFromRemainingArguments=$false, Position=2)] [ValidateNotNull()] [ValidateNotNullOrEmpty()] [ValidateSet('FULL', 'DIFF', 'LOG')] [string]$Type, # The Share [Parameter(Mandatory=$true,HelpMessage='This is the root of the backup directory', ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, ValueFromRemainingArguments=$false, Position=2)] [ValidateNotNull()] [ValidateNotNullOrEmpty()] [String]$Share, #If you wish to see the results of the query [switch]$OutputResults, # Compression off ? [switch]$CompressionOff, # Copy Only ? [switch]$CopyOnly ) DynamicParam { # Set the dynamic parameters' name $ParameterName = 'Database' # Create the dictionary $RuntimeParameterDictionary = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameterDictionary # Create the collection of attributes $AttributeCollection = New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute] # Create and set the parameters' attributes $ParameterAttribute = New-Object -TypeName System.Management.Automation.ParameterAttribute $ParameterAttribute.Mandatory = $true $ParameterAttribute.Position = 1 # Add the attributes to the attributes collection $AttributeCollection.Add($ParameterAttribute) # Generate and set the ValidateSet # Load the assembly [void][reflection.assembly]::LoadWithPartialName( 'Microsoft.SqlServer.Smo' ) $srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Instance $arrSet = $Srv.databases.Name $srv.ConnectionContext.Disconnect() $ValidateSetAttribute = New-Object -TypeName System.Management.Automation.ValidateSetAttribute -ArgumentList ($arrSet) # Add the ValidateSet to the attributes collection $AttributeCollection.Add($ValidateSetAttribute) # Create and return the dynamic parameter $RuntimeParameter = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter -ArgumentList ($ParameterName, [string], $AttributeCollection) $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter) return $RuntimeParameterDictionary } begin { # Bind the parameter to a friendly variable $Database = $PsBoundParameters[$ParameterName] if($CompressionOff) { $Compression = 'N' } else { $Compression = 'Y' } if($CopyOnly) { $Co = 'Y' } else { $CO = 'N' } } Process { $Query = @" EXECUTE [master].[dbo].[DatabaseBackup] @Databases = '$Database', @Directory = N'$Share', @BackupType = '$Type', @Verify = 'Y', @CheckSum = 'Y', @Compress= '$Compression', @LogToTable = 'Y', @CopyOnly = '$CO' "@ if ($pscmdlet.ShouldProcess("$Instance" , "Back up $Database")) { try { $srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Instance $SqlConnection = $srv.ConnectionContext if($OutputResults) { Register-ObjectEvent -InputObject $SqlConnection -EventName InfoMessage -SupportEvent -Action { Write-Host $Event.SourceEventArgs } } $SqlConnection.StatementTimeout = 8000 $SqlConnection.ConnectTimeout = 10 $SqlConnection.Connect() $SqlConnection.ExecuteNonQuery($Query) Write-Output -InputObject "$Database has been backed up to $Share" } catch { Write-Warning -Message ("Something went Wrong. Run `$Error[0] | Fl -force to work out why") $Query } } } End { $srv.ConnectionContext.Disconnect() } } |