Back up SQL Server database with SQL Server Objects(SMO) using PowerShell It is easy to set up SQL Server Agent jobs to backup database in timely fashion.
But why not in PowerShell while SMO is exposed in .NET assembly?

Usage:
Backup-Database -dbName [string] -deviceNames [string[]] [-deviceType [Microsoft.SqlServer.Management.Smo.DeviceType]] [-Overwrite [Switch]] [[-Differential] [Switch]]

Parameters:

• -dbName: Database name to back up e.g.) pubs, AdventureWorks, NorthWind
• -deviceNames: tape back up device name or File system name e.g.) c:/bak/pubs.bak, c:/bak/pubs_diff.bak
• -deviceType: One of "LogicalDevice, Tape, File, Pipe, VirtualDevice"
• -Overwrite: overwite existing backup set
• -Differential: create a differential backup
  

Examples:


There are times that you do not know all the device types available,
then simply enter invalid device type
A great feature of PowerShell is that PowerShell will enumerate DeviceType
and kindly give you list of valid device type names when invalid type is entered

[^_^]PS[597]>Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:DontKnow
Backup-Database : Cannot convert value "DontKnow" to type "Microsoft.SqlServer.Management.Smo.DeviceType" due to invali
d enumeration values. The possible enumeration values are "LogicalDevice, Tape, File, Pipe, VirtualDevice".
At line:1 char:73
+ Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:D <<<<>

Prerequisites:

1. 
   Microsoft SQL Server 2005 Management Objects Collection: You need to download and install one of SQLServer2005_XMO_.msi versions
2. How to create $server variable: Refer to Scripting Database Objects using SMO (Updated) on SqlTeam.com

Source:

# author: Sung Kim
# Description: Create Full or Differential backups for 
#   SQL Server database with SQL Server Objects(SMO) using PowerShell
function Backup-Database {
  param([string]$dbName = $(throw 'Enter Database name to backup'), 
       [string[]]$deviceNames = $(throw 'Enter device name(s)'),
       [Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType = 
         [Microsoft.SqlServer.Management.Smo.DeviceType]File,
       [Microsoft.SqlServer.Management.Smo.Server]$server,
       [switch]$Overwrite = $false,
       [switch]$Differential = $false)
  
  trap { break; }
  
  # Load SMO assembly
  $PRIVATE:SmoDir = 
    'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies'
  [System.Reflection.Assembly]::LoadFile(
    "$PRIVATE:SmoDir\Microsoft.SqlServer.Smo.dll") | out-null
  
  # Initialize backup object
  $PRIVATE:bkp = New-Object Microsoft.SqlServer.Management.Smo.Backup
  $PRIVATE:bkp.Database = $dbName
  $PRIVATE:bkp.Initialize = $Overwrite
  $PRIVATE:bkp.Incremental = $Differential
  
  # Add backup devices to backup database to
  foreach ($PRIVATE:deviceName in $deviceNames) {
    $PRIVATE:bkpItem = 
      new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem(
        $PRIVATE:deviceName, 
        [Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType)
    $PRIVATE:bkp.Devices.Add($PRIVATE:bkpItem)
  }

  if ($Overwrite) {
    Write-Warning "Overwriting existing backup set"
  }
  
  $PRIVATE:bkp.SqlBackup($server)
  
  if ($Differential) {
    Write-Host -NoNewLine -F Blue "Differential Backup: "
  }
  Write-Host -NoNewLine "$($dbName) has been backed up to "
  Write-Host -ForegroundColor Cyan -NoNewLine "$($deviceNames)" 
  Write-Host " successfully"
}

Tags : Monad msh PowerShell SMO  ¶ 11:29 AM 10 comments

  Re-inventing the wheel for fun. I was going through Community-Submitted Scripts Center on Microsoft TechNet and came across a script named, "BackupFiles.ps1" by Greg Lyon.

The script is functional and works.
But I didn't like the fact that it looked more like a VBScript script than a PowerShell script.
Moreover, the script was heavily utilizing a COM object instance of type "Scripting.FileSystemObject".

My goal was simply to change the code to make it look more PowerShell-like(uhm, this should mean different for different people, but in my case, i tend to use "PowerShell-Like" when a script is written mostly with built-in (cmdlets -and functions)). Nothing less, nothing more.

Re-inventing the wheel for fun.

Modified mostly "function BackupFolder" part

# BackupFiles.ps1
# Greg Lyon - July 2006
# reference: http://www.microsoft.com/technet/scriptcenter/csc/scripts/backup/backup/cscbk013.mspx
# Modified by DBMwS - July 14, 2006

param($strSourceFolder = "C:\programming\ps\test",
    $strDestinationFolder = "F:\backup\programming\ps\test")

#{{{ function BackupFolder
function BackupFolder
{
    param([string] $SourceDir = $(throw 'Enter Source Directory Name'),
        [string] $DestinationDir = $(throw 'Enter Desitnation Directory Name'))

    if (! (test-path $SourceDir)) {
        throw "$($SourceDir) is not a valid directory!"
    }
    if (! (test-path $DestinationDir)) {
        throw "$($DestinationDir) is not a valid directory!"
    }

    # Check if destination directory is missing any files in the source
    # directory.if any file is missing, copy them over to the backup dir. 
    get-childitem $SourceDir |
    foreach {
        if ($_.PsIsContainer) {
            $childDstDir = (join-path $DestinationDir $_.Name)
            if (! (test-path $childDstDir)) { [void](mkdir $childDstDir) }
            BackupFolder $_.FullName $childDstDir
        } else {
            $srcFile = $_.FullName
            $destFile = join-path $DestinationDir $_.Name

            if (test-path $destFile) {
                $timeDiff = ($srcFile.LastWriteTime - $destFile.LastWriteTime).TotalSeconds
                if ($timeDiff -gt 2) {
                    copy-item $srcFile $destFile -Force
                    write-host "Copied file $($srcFile) to $($destFile)"
                    $script:iCopyCount++
                }
            } else {
                copy-item $srcFile $destFile -Force
                write-host "Copied file $($srcFile) to $($destFile)"
                $script:iCopyCount++
            }
        }
    }

    # Check if Source dir is missing any files on the backup dir.
    # if there is/are file(s) missing, copy them over from backup dir to src dir
    get-childitem $DestinationDir |
    foreach {
        if ($_.PsIsContainer) {
            $childSrcDir = (join-path $SourceDir $_.Name)
            if (! (test-path $childSrcDir)) { [void](mkdir $childSrcDir) }
            BackupFolder $childSrcDir $_.FullName
        } else {
            $srcFile = join-path $SourceDir $_.Name
            if (! (test-path $srcFile)) {
                remove-item $_
                write-host "Deleted file $($_)"
                $script:iDestDeletedCount++
            }
        }
    }
}
#}}}


#{{{ function WaitKey
function WaitKey {
  param( [String] $strPrompt = "Press any key to continue ... ")
  Write-Host
  Write-Host $strPrompt -NoNewline
  $key = [Console]::ReadKey($true)
  Write-Host
}
#}}}


#{{{ MAIN BODY
$iCopyCount = 0
$iDestDeletedCount = 0

Write-Host
Write-Host "Backing up " -NoNewline -ForegroundColor "White"
Write-Host $strSourceFolder -ForegroundColor "Cyan" -NoNewline
Write-Host " to " -NoNewline -ForegroundColor "White"
Write-Host $strDestinationFolder -ForegroundColor "Cyan"
Write-Host


if (! (test-path $strSourceFolder)) {
    Write-Host "Error: source folder does not exist!" -ForegroundColor "Red"
    Write-Host
    Write-Host "Exiting script"
    WaitKey "Press any key to exit ... "
    exit
}

if (! (test-path $strDestinationFolder)) {
    Write-Host "Warning: destination folder`($($strDestinationFolder)`) does not exist"
    $p = Read-Host "Create folder and continue? "
    Write-Host

    if ( $p[0] -ieq 'y' ) {
    new-item -Type Directory -Path $strDestinationFolder | out-null
  } else {
        Write-Host "Exiting script"
        WaitKey "Press any key to exit ... "
        exit
  }
}

BackupFolder $strSourceFolder $strDestinationFolder

if( ($iCopyCount -eq 0) -and ($iDestDeletedCount -eq 0) ) {
  Write-Host
  Write-Host "Folders are synchronized" -ForegroundColor "magenta"
} else {
  Write-Host
  Write-Host $iCopyCount "files copied from source to destination" -ForegroundColor "magenta"
  Write-Host $iDestDeletedCount "orphaned destination files deleted" -ForegroundColor "magenta"
}

WaitKey "Press any key to exit ... "

#}}}

Tags : Monad msh PowerShell  ¶ 10:57 PM 1 comments