param($datasource, $username, $password, $backupPath)

# set "Option Explicit" to catch subtle errors
set-psdebug -strict

# you can opt to stagger on, bleeding, if an error occurs
$ErrorActionPreference = "stop"

# create backup directory if not exist
New-Item -ItemType Directory -Force -Path $backupPath | Out-Null
cd $backupPath

# get current date in yyyyMMdd format
$currentDate = Get-Date -format yyyyMMdd
Write-Output "Current Date = $currentDate"

# get the latest backup directory
Get-ChildItem -Directory -Path . | Sort-Object CreationTime -Descending | Select-Object -First 1 -ExpandProperty Name -OutVariable latestDir | Out-Null
Write-Output "Latest Backup Directory = $latestDir"

# get the latest version of today's backups
$currentVersion = 0
if ($latestDir -Match $currentDate)
{
    $currentVersion = $latestDir.Substring($currentDate.Length + 1)
}
Write-Output "Current Version = $currentVersion"

# create directory for the next version of backup
$nextVersion = [int]$currentVersion + 1
Write-Output "Next Version = $nextVersion"
$nextVersionDir = "$($backupPath)\$($currentDate)_$($nextVersion)"
New-Item -ItemType Directory -Force -Path $nextVersionDir | Out-Null

# load SMO assembly
$msPrefix = 'Microsoft.SqlServer'
[System.Reflection.Assembly]::LoadWithPartialName("$msPrefix.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("$msPrefix.SMOExtended") | out-null
$smoPrefix = "$msPrefix.Management.Smo"

# set connection
$server = New-Object ("$smoPrefix.Server") $datasource
$server.ConnectionContext.LoginSecure = $false
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_Password($password)

# check connection
if ($server.Version -eq $null ) {Throw "Can't find the instance $datasource"}

# loop through databases names to do back up
foreach ($database in $server.databases)
{
    # skip system databases
    if ($database.Name -in 'Master','Model','MSDB','TempDB','SSISDB','distribution','ReportServer','ReportServerTempDB') {continue}

    # skip offline databases
    if ($database.Status -ne 'Normal') {
        Write-Output ("Skipping Offline: {0}" -f $database.Name)
        continue
    }

    $backupFile = "$($nextVersionDir)\$($database.Name).sql"
    Write-Output "Start to back up $($database.Name)"

    # manually add create database sql statement at the beginning of the sql file
    New-Item $backupFile -ItemType File | Out-Null
    Add-Content $backupFile "USE [master]"
    Add-Content $backupFile "GO"
    Add-Content $backupFile "Drop Database if exists [$($database.Name)];"
    Add-Content $backupFile "GO"
    Add-Content $backupFile "USE [master]"
    Add-Content $backupFile "GO"
    Add-Content $backupFile "CREATE DATABASE [$($database.Name)]"
    Add-Content $backupFile "GO"
    Add-Content $backupFile "USE [$($database.Name)]"
    Add-Content $backupFile "GO"
    Add-Content $backupFile ""

    # set export configuration
    $transfer = New-Object ("$smoPrefix.Transfer") $database
    $transfer.Options.ScriptSchema = $true
    $transfer.Options.ScriptData = $true
    $transfer.Options.ScriptBatchTerminator = $true
    $transfer.Options.ToFileOnly = $true
    $transfer.Options.AppendToFile = $true
    $transfer.Options.Encoding = New-Object ("System.Text.UTF8Encoding")
    $transfer.Options.AllowSystemObjects = $false
    $transfer.Options.Permissions = $true
    $transfer.Options.SchemaQualify = $true
    $transfer.Options.ExtendedProperties = $true
    $transfer.Options.DRIAll = $true
    $transfer.Options.Indexes = $true
    $transfer.Options.Triggers = $true
    $transfer.Options.IncludeHeaders = $true
    $transfer.Options.IncludeIfNotExists = $true
    $transfer.Options.Filename = "$backupFile"
    $transfer.Options.EnforceScriptingOptions = $true

    # start scripting
    $transfer.EnumScriptTransfer()

    Write-Output "Finished backing up $databaseName to $backupFile"
}