Simulating Log Shipping with Windows PowerShell

Over the past few months I have been writing the high availability and desaster recovery documentation and procedures. When I first took this position we had no sort of disaster recovery plan in place. We used tape backups and SQL Server backups that were only done once a day. T-SQL log backups were done only once during the day and there was nothing set up to notify the database administrator (that is me) if one of the jobs failed. I also had nothing to restore the backup to, if there was a hardware failure until the replacement equipment was received. Add to the fact that the SQL Server itself is out of any sort of warranty and we had a potentially nast situation if something did go wrong.

In addition to changing the backup plans so that we are doing t-log backups every hour and adding alerts, operators, and notifications tot he server I started kicking around teh idea of using transaction log shipping to add an extra layer of protection to the system. But the issue was the production database server is SQL Server 2000 Standard, which does not include log shipping.

I decided to write a PowerShell script that would use the backups I was alreeady creating to simulate log shipping to a warm standby server. Here is what I have so far.

#############################################
## SQL Server Related variables. ##
#############################################
$ErrorActionPreference = "SilentlyContinue"
$DebugPreference = "Continue"
#SQL Server to which the database is being restored.
$SQLServer = "sqlserver"
#Name of the database being restored.
$database = "database"
#The connection string used to access teh SQL Server. Do not change.
$ConnectionString = "Server = $SQLServer; Database = master; Integrated Security = True"
#Time in seconds the script should waite for the non-query to finish executing.
$TimeOut = 1800 #1800 = 30 minutes. This is more than enough time in my environment.
#############################################
# Email Configuration Variables ##
#############################################
$EmailSub = "PSLogShipping Status Report"
$MsgText = "Included is the list of backups/logs applied and the event log."
$EmailServer = "exchange"
$EmailTo = @(email@email.com)
$emailFrom = "email@email.com"
##############################################
## General Configuration variables. ##
##############################################
#Path to the list used to keep track of logs already done.
$ListPath = "C:\script\AppliedLogs.csv"
$header = "Name, LastWriteTime, TimeApplied"
if(!(Test-Path $ListPath)){ $header | Out-File $ListPath }
$EventLog = "C:\script\EventLog.txt"
if(!(Test-Path $EventLog)){ $null | Out-File $EventLog }
#An array used by the email finction to attach the files.
$emailLogs = @($ListPath, $EventLog)
#Path to the logs.
$path = "\\server\share\"
#Test tobe sure tha path above is valid.
if(!(Test-Path $path)){ Write-Host "The path to the backup files/logs cannot be found." }
#The hour at which your list should be cleared.
$ClearList = 15

###############################################
## Do not change anything under this section ##
## unless you know what you are doing. ##
###############################################
function Send-Email()
{
param( [string]$smtpServer,
[string]$from,
[string[]]$recipient,
[string]$subject,
[string]$body,
[string[]]$attachment,
[bool]$isHTML
)

$msg = new-object Net.Mail.MailMessage
$msg.From = $from
foreach($person in $recipient)
{
$msg.To.Add($person)
}
$msg.Subject = $subject
$msg.Body = $body
$msg.IsBodyHTML = $isHTML
foreach($item in $attachment)
{
$msg.Attachments.Add($item)
}
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($msg)

}
#Append to log.
$today = Get-Date
$today.ToString() + " Starting...." | Out-File $EventLog -Append
if($today.Hour -lt $ClearList)
{
$BackupDone = $false

##Get the primary backup.
$files = get-childitem $path | where { $_.Name -match ".bak" }
$backup = $files | sort -desc LastWriteTime

#Set the date we need to work with to determine which t-logs we will look at.
#We only look at ones after the most recent backup.
if($backup.Length -gt 1)
{
$BackupDate = $backup[0].LastWriteTime
$BackupFile = $backup[0].Name
}
else
{
$BackupDate = $backup.LastWriteTime
$BackupFile = $backup.Name
}


##Get the list of t-logs. Only logs with a date greater than that of the backup.
$logs = Get-ChildItem $path | where { ($_.Name -match ".trn") -and ($_.LastWriteTime -gt $BackupDate) }

#Read the list of log files already applied.
$list = Import-Csv $ListPath

#Remove any logs/backup that have already been applied from our active list.
foreach($item in $list)
{
#Has the backup been applied yet?
if($BackupFile -eq $item.Name)
{
$logs = @($logs | Where-Object {$_.Name -ne $item.Name})
$BackupDone = $true
}
$logs = @($logs | Where-Object {$_.Name -ne $item.Name })

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Done Getting backup and transaction logs for list." | Out-File $EventLog -Append
}


##Connect to SQL Server

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $ConnectionString
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLCmd.Connection = $SQLConnection
$SQLCmd.CommandTimeout = $TimeOut
Write-Debug "Attempting to open connection."

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Attempting to open connection." | Out-File $EventLog -Append
$SQLConnection.Open()
$time = Get-Date -displayhint time
$time.toString() + " Connection Open." | Out-File $EventLog -Append



if($BackupDone -eq $true)
{ #Apply the logs

foreach($log in $logs)
{
Write-Debug "Attempting to execute Non-Query."
$SQLcmd.CommandText ="RESTORE DATABASE $database FROM DISK ='$path\$log' WITH NORECOVERY"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database FROM DISK ='$path\$log' WITH NORECOVERY" | Out-File $EventLog -Append

#Append the log info to the list.
$CurrentDate = Get-Date
$text = "`n"+$log.Name+", "+$log.LastWriteTime+", "+$CurrentDate
$text | Out-File $ListPath -Append
$text | Out-File $EventLog -Append
}

}
else
{
#Apply the backup
Write-Debug "RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE"
$SQLcmd.CommandText = "RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE" | Out-File $EventLog -Append
#Append the backup file to the list.
$CurrentDate = Get-Date
$text = "`n"+$BackupFile+", "+$BackupDate+", "+$CurrentDate
$text | Out-File $ListPath -Append
$text | Out-File $EventLog -Append
}
#Close connection to server
Write-Debug "Closing Connection."
$SqlConnection.Close()
#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Connection Closed." | Out-File $EventLog -Append
"------------------------------------" | Out-File $EventLog -Append

}
else
{
##Connect to SQL Server 
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server = $SQLServer; Database = master; Integrated Security = True"
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLCmd.Connection = $SQLConnection
$SQLCmd.CommandTimeout = $TimeOut
$SQLConnection.Open()
#Recover the database from the backup process.
Write-Debug "RESTORE DATABASE $database WITH RECOVERY"
$SQLcmd.CommandText = "RESTORE DATABASE $database WITH RECOVERY"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database WITH RECOVERY" | Out-File $EventLog -Append
#Close connection to server
$SqlConnection.Close()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Connection Closed." | Out-File $EventLog -Append
"------------------------------------" | Out-File $EventLog -Append
#Send the event log and the list of applied DBs.
Send-Email -smtpServer $emailServer -from $emailFrom -recipient $emailTo -subject $EmailSub -body $MyText -attachment $emailLogs -isHTML $true

#Sleep 15 seconds so the email can be sent.
Start-Sleep -s 15

#Clear the list.
Write-Host "Purging list.......`n"
$header = "Name, LastWriteTime, TimeApplied"
$header | Out-File $ListPath 
Write-Host "Done!"
}