Simulating SQL Log Shipping

RobertKaucher

So I have not found a simple resource that I liked that would allow me to do log shipping between a warm standby SQL Server 2005 server and a production SQL Server 2000 Standard server. Because of this I have decided to write a PowerShell script that will run on the standby server and simulate the log shipping. Here is what I have so far, and I was wondering if you guys might have some advice.

SQL1 Full backup done at 10:00 PM, t-logs begin at 6:00 AM done every hour untill 9:00PM
SQL2 Warm standby with all users replicated from SQL1
BU1 The server where the backups are stored.

The script will begin at 05:30 every day and run every hour until 22:30

Here is the logic:
If the current time is > 22:00 clear the list of files that have been restored.

If the time is < 22:00 check the list of files and compare it to the list of files on the backup server.
** Find the most recent backup.
** List any t-logs that were generated after this backup.

Restore any files that are not on the list using the NO RECOVERY option.
Add those files to the list.
Send an email on either success or failure.

I have been testing it by printing the RESTORE command to host. So I know that it works, I'm just not sure if this is even a good idea... The only other way I can think of to do it is to write some stored procedures and PowerShell is far simpler, IMO. Does this make sense? Am I over thinking?

RobertKaucher

I'm in the testing phase, and I know have to figure out how to deal with this:

PS C:\script> C:\script\LogShipping.ps1
RESTORE DATABASE dbname FROM DISK ='\\bu1\sql$\dbname\dbname_db_201002172200.BAK' WITH NORECOVERY, REPLACE
Exception calling "ExecuteNonQuery" with "0" argument(s): "Timeout expired. The timeout period elapsed prior to comple
tion of the operation or the server is not responding."
At C:\script\LogShipping.ps1:88 char:26
+ $SQLcmd.ExecuteNonQuery( <<<< )

Any suggestions? I don't want to catch the exception, I can deal with thandling the error. I would prefer to extend the timeout so that the exception is not thrown. The DB is restoring. But my concern is that once I start processing the T-logs the script will fail as it will move forward before the first t-log has been processed...

EDIT: I have added $SQLCmd.CommandTimeout = 1800 to the script. I am testing now.

RobertKaucher

The program seems to be working perfectly now. I am going to run some more tests and then I will share the code.

RobertKaucher

Here is what I have. I have been testing it for a few days. Seems to work fine for what I want. I make no warranty that it will not FUBAR something. So please test before you use. I am currently working on making the SQL connection and query exec via a parametrized function. I just need to work out how I want it to work in the script.

I am also thinking of doing a version that uses SMO and WMI to write to actual event logs, etc. Right now there is no real error handling. I also want to include that.

#############################################
## 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!"
}