Shared Points

items of some interest

PowerShell - sorting files by datestamp for SQL backup/restore

PowerShell - sorting files by datestamp for SQL backup/restore

  • Comments 3
  • Likes

# 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
  • What would the XML look like that is read by this script?

  • <?xml version="1.0"?>

    <SQLserver>

    <LoadTestReset>

    <Database>

    <DBName>name_of_DB_goes_here</DBName>

    <InstanceName>(local)</InstanceName>

    </Database>

    </LoadTestReset>

    </SQLserver>

  • 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>

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment