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]>"
}
Comments
Anonymous
January 01, 2003
<?xml version="1.0"?> <SQLserver> <LoadTestReset> <Database> <DBName>name_of_DB_goes_here</DBName> <InstanceName>(local)</InstanceName> </Database> </LoadTestReset> </SQLserver>Anonymous
December 14, 2012
What would the XML look like that is read by this script?Anonymous
December 19, 2012
Few more questions. LoadTestReset would be your Server Name? InstanceName would be named instance and/or default instance (local)? Would setting instance to remote server run against the remote server? Would you add a new row for each dbname element or would you use the same row separating each dbname by a comma? e.g. below. <?xml version="1.0"?> <SQLserver> <LoadTestReset> <Database> <DBName>name_of_DB1_goes_here</DBName> <DBName>name_of_DB2_goes_here</DBName> <DBName>name_of_DB3_goes_here</DBName> <DBName>name_of_DB4_goes_here</DBName> <InstanceName>(local)</InstanceName> </Database> </LoadTestReset> </SQLserver> or <?xml version="1.0"?> <SQLserver> <LoadTestReset> <Database> <DBName>name_of_DB1_goes_here,name_of_DB2_goes_here,name_of_DB3_goes_here,name_of_DB4_goes_here</DBName> <InstanceName>(local)</InstanceName> </Database> </LoadTestReset> </SQLserver>