WARNING: Use caution in running scripts you find on the internet. You are responsible for the well being of your systems!

We’re sitting around waiting for the SAN monkeys to fling some crap our way – not the SAN guys, the SAN monkeys, kinda like gremlins, but not as bad. Our primary cluster is down, which runs 100+ databases. Maybe the disks will come up – or maybe they won’t.

Assessing the situation, I found that the MSDB was not in full recovery mode. People, please don’t run around changing DBs to simple to ‘save space’ without thinking the ramifications through. In this case, we lose all backup history so we now need to figure out what backup file is the most recent and then which log files come after that .bak file – then we slam them together into a file, leaving the NORECOVERY clause off the last log restore statement. If you think I’m doing this by hand, you’re crazy.

We make an assumption here. Each file holds only one backup set. This is how we do our backup files, so I’m OK with this assumption.


$DatabaseBackupRoot = “\\MYSERVA\e$\Backups\"
$fix = ""

$DatabaseBackupDirs = gci $DatabaseBackupRoot

foreach ($DatabaseBackupDir in $DatabaseBackupDirs)
{
$DB = $DatabaseBackupDir.Name

$BackupRestore = ""
$LogRestore = ""
$LogRestore = ""
$BackupRestore = ""
# Parameters

$Path = “\\MYSERVA\e$\Backups\$DB”;
$FullPattern = “*.bak”;
$LogPattern = “*.trn”;

# Enumerate Last Full Back
$FullBackup = Get-ChildItem $Path -Filter ($DB + $FullPattern) `
| sort Name `
| Select-Object -Last 1;

# Enumerate Log Backups
$LogBackups = Get-ChildItem $Path -Filter ($DB + $LogPattern) `
| Where-Object { $_.LastWriteTime -gt $Fullbackup.LastWriteTime} `
| sort Name;

$LogBackupsCount = $LogBackups.Count

$BackupFileName = $FullBackup.Name
# if the backupfile is null, skip this
if ($BackupFileName)
{

if ($LogBackupsCount > 0)
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"
}

}
else{
#We get a list in the output of emtpy directories.
Write-Output $DB
}

$counter = 0
$len = $LogBackups.Count

foreach ($LogBackup in $LogBackups)
{
$counter++

$LogBackupFile = $LogBackup.Name;
#Test for no log/empty directory
if($LogBackupFile)
{
if($counter -lt $len)
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"

}
}

}

$RestoreOneDatabase = $BackupRestore + $LogRestore

$RestoreAllDatabases = $RestoreAllDatabases + $RestoreOneDatabase

}

$RestoreAllDatabases | out-file "c:\temp\restore-MYSERVA.sql"

So now we’ve got a SQL statement with all these restores compiled together and separated with GO statements. Pretty neat. I’d probably run small blocks of them and fix any issues the script might have.