PowerShell - sorting files by datestamp for SQL backup/restore

Article
11/03/2012

# Do-SPSQLbackupRestore.ps1
# usage:
# DO-SQLBackupRestore <Backup|Restore> [<SQL instance>] [<XMLfilename]>
#
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

Function DO-SQLBackupRestore {
param ([string] $strSQLaction="Backup", [string] $strSQLServerInstance="(local)", [string] $DataBasesXML="C:\BKP\BKP-SQLdatabases.xml")

$SQLserver = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $strSQLServerInstance
$xml = [XML] (get-content $DataBasesXML)

## dbName_yyyyMMddHHmmss.BAK
$stamp = "yyyyMMddHHmmss"
if ($strSQLaction.ToUpper() -eq "BACKUP") { DoBackupSQL $SQLserver $xml }
else { if ($strSQLaction.ToUpper() -eq "RESTORE") { DoRestoreSQL $SQLserver $xml }
else { write-output " options are BACKUP or RESTORE" } }
}

##########################################################

#
# do a backup of all the selected SQL files (those specified in the XML file)
#
function DoBackupSQL { PARAM ($SQLserver, $xml)
$backupDirectory = $SQLserver.Settings.BackupDirectory
foreach ($database in $SQLserver.Databases) {
    # if this DB is in our XML file, then we should back it up.
    foreach($LoadTestDB in $xml.SQLserver.LoadTestReset.Database) {
      if ($($database.Name) -eq $($LoadTestDB.DBName)) {
        $timestamp = Get-Date -format $stamp
        $dbNameStamped = $($database.Name) + "_" + $timestamp + ".bak"
        $backupPathname = $backupDirectory + "\" + $dbNameStamped
        write-output "Backup of $($database.Name) to $backupPathname"
        $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
        $smoBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
        $smoBackup.BackupSetDescription = "Full Backup of " + $database.Name
        $smoBackup.BackupSetName = $($database.Name) + " Backup"
        $smoBackup.Database = $($database.Name)
        $smoBackup.MediaDescription = "Disk"
        $smoBackup.Devices.AddDevice($backupPathname, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
        $smoBackup.SqlBackup($SQLserver)
      }
    }
}
}

#
# restore the files off disk back into SQL if they are in our XML target list.
#
function DoRestoreSQL { PARAM ($SQLserver, $xml)
$backupDirectory = $SQLserver.Settings.BackupDirectory
$RestoreFiles = FindLatestBAKfiles $backupDirectory
foreach ($file in $RestoreFiles) {
     # if this DB is in our XML file, then we should restore it.
     foreach($LoadTestDB in $xml.SQLserver.LoadTestReset.Database) {
       if ($($file.DBname) -eq $($LoadTestDB.DBName)) {
         write-output "Restore of $($file.DBname) from $($file.fullpath)"
         $smoRestore = New-Object ("Microsoft.SqlServer.Management.Smo.Restore")
         $smoRestore.NoRecovery=$false
         $smoRestore.ReplaceDatabase=$true
         $smoRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
         $smoRestore.Devices.AddDevice($($file.fullpath),[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
         $smoRestore.Database=$($file.DBname)
         $smoRestore.SqlRestore($SQLserver)
       }
    }
}
}

#
# get a dir listing of all the backup files that are in $backupDirectory,
# sort on filename, on timestamp and grab the newest of each
# I am assuming multiple copies of each DB, as dbName_yyyyMMddHHmmss.BAK so obviously we want the most recent.

function FindLatestBAKfiles { PARAM ([string] $backupDirectory)
$stamp = "yyyyMMddHHmmss"
$suffix="_$stamp.bak"
$regex="^?_\d{$($stamp.length)}.bak"
$fobs = @()
# grab files that match the RegEx pattern used for our backups.
$files=Get-ChildItem $backupDirectory | % {if ($_.name -match $regex) {$_}}
ForEach ($file in $files) {
    if ($file.name -match '(?<FileName>[^\t\n\r\f]+)_(?<FileDate>\d+).bak$') {
      $date = $matches.FileDate
      $fileName = $matches.FileName
      #is the datetime used legitimate? if so, we will grab it.
      if ( $date -match '(?<Year>[0-9]{4})(?<Month>[0-9]{2})(?<Day>[0-9]{2})(?<Hour>[0-9]{2})(?<Minute>[0-9]{2})(?<Seconds>[0-9]{2})') {
        $fileDate = [datetime]"$($matches.Year)-$($matches.Month)-$($matches.Day) $($matches.Hour):$($matches.Minute):$($matches.Seconds)"
        # all good so add this to out list of files we want.
        $fobj = New-Object System.Object
        $fobj | Add-Member -MemberType NoteProperty -Name fileName -Value $fileName
        $fobj | Add-Member -MemberType NoteProperty -Name fullname -Value $file.Name
        $fobj | Add-Member -MemberType NoteProperty -Name fileDate -Value $fileDate
        $fobj | Add-Member -MemberType NoteProperty -Name fullpath -Value $file.VersionInfo.FileName
        $fobs += $fobj
      }
    }
}
# now we have all the properties of the files, so next is sort them and pump out the first of each
$files = $fobs | sort-object -Property @{Expression="fileName";Descending=$false},@{Expression="fileDate";Descending=$true} | group filename
# whew! wasn't that easy.
# now we can build an object with the unique entries of the latest files we wish to restore.
$FilesToRestore=@()
if ($files) {
   foreach ($file in $files) {
     $robj = New-Object System.Object
     $robj | Add-Member -MemberType NoteProperty -Name fullname -Value $file.Group[0].fullName
     $robj | Add-Member -MemberType NoteProperty -Name fullpath -Value $file.Group[0].fullpath
     $robj | Add-Member -MemberType NoteProperty -Name DBname   -Value $file.Group[0].fileName
     $FilesToRestore += $robj
   }
}
return $FilesToRestore

}

if ($args.count -gt 0) {
DO-SQLBackupRestore $args
}
else {
write-output " usage:"
write-output " DO-SQLBackupRestore <Backup|Restore> [<SQL instance>] [<XMLfilename]>"
}

PowerShell - sorting files by datestamp for SQL backup/restore

Additional resources