PowerShell - sorting files by datestamp for SQL backup/restore
# 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]>"
}