Log-Shipping SQL Express with PowerShell

Someone on Server Fault asked a question about which versions of SQL Server support log shipping.  He uses Express.

Log shipping uses SQL Agent, and Express doesn’t come with SQL Agent, but you can still manually log ship with PowerShell and Task Scheduler.  Use the log backup script of your choice (I like Ola Hallengren’s, which can also be used with Task Scheduler), either back up to a share or copy the files to a share (perhaps with robocopy /mir?), and then run this or something like this (for a read-only copy):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$standby=”C:\path\to\ROLLBACK_UNDO_YourDB.BAK”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  STANDBY = N'” + $standby + “‘,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

Or this (for an offline warm standby):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

(If you’re copying and pasting, don’t forget to fix the “smart quotes,” which aren’t smart.)

Leave a Comment

Filed under powershell

Leave a Reply

Your email address will not be published. Required fields are marked *