In 2008, I was privileged to be a part of a project to write a chapter for a book that was for a good cause. SQL Server MVP Deep Dives became an avenue for a lot of the SQL Server MVPs to share their expertise for the benefit of not just the technical community but of the beneficiary.
I wrote a chapter for the book based on this blog post some three years ago and one of the recommendations I did was to convert the VBScript scripts to Windows PowerShell. So, here it is.
I’ve converted the VBScript that does the checking of the folders containing my latest LOG backups and restoring them in NORECOVERY mode to Windows PowerShell. What is fascinating is the fact that if the process I’ve outlined in the blog post is followed thru, adding another database to be configured for log shipping was as easy as creating regular LOG backups via TSQL scripts or database maintenance plans – no need to add an entry for every database that will be configured. How cool was that?
So, here’s the Windows PowerShell version of the custom restore LOG backup command
$logBackupFolder="your LOG backup location here or a parameter variable"
##Variable for time duration - the amount of time you need to generate and restore LOG backups
$1HourOld = [DateTime]::Now.AddHours(-1)
##Retrieve folders and files in the specified directory
foreach ($databaseName in (Get-ChildItem $logBackupFolder | Where { $_.PsIsContainer }) )
{
foreach ($logName in (Get-ChildItem $logBackupFolder$databasename | where {($_.CreationTime -ge $1HourOld) -and ($_.Extension -eq ".trn")} SELECT name))
{
$logBackupPath = [System.String]::Concat("'$logBackupFolder",$databasename,"",$logName.Name,"'")
$restoreSQL = "RESTORE LOG $databaseName FROM DISK=$logBackupPath WITH NORECOVERY" | Out-File D:$databaseName.sql
Invoke-Expression "osql.exe -SYourSQLServerInstanceHere -E -iD:$databaseName.sql"
}
}
You might be wondering why I am using osql.exe instead of sqlcmd.exe. Well, it’s still there even if you have SQL Server 2008. Which means this script will work even for SQL Server 2000 instances as long as you install Windows PowerShell on the machine running SQL Server.
And all of that for a mere 17-liner script. I could write it even less than that but I’m a big fan of script readability. Now, that’s a good enough reason to dig deeper on what Windows PowerShell has to offer
>Hey Sir, ever thought of doing compressed backup replication? Here is my open disaster recovery code: http://www.simple-talk.com/sql/backup-and-recovery/disaster-recovery-for-sql-server-databases-/
>Hi Hugo,I've read your documentation a lot of times and it is really, really impressive. As somebody who is a bit of a paranoid when it comes to security, I just don't like enabling xp_cmdshell on my TSQL scripts 🙂
not sure if its just me but you seem to be missing a couple of pipes ( | ) in your script….
ie.
Get-ChildItem $logBackupFolder$databasename where {($_.CreationTime
should be
Get-ChildItem $logBackupFolder$databasename | where {($_.CreationTime
great script nonetheless!!!
Hi Chris,
This blog post was imported from Blogger and from the post, the PowerShell scripts are being modified after being posted. I guess it has something to do with HTML conversions. Thanks for pointing that out.
I’ve updated the blog post to reflect the missing pipelines. Hopefully, I won’t have the same issues in future blog posts
Hi Edwin,
If you’re using wordpress (as I do) then try wrapping the code like
.
I’ve had similar issues when just using HTML to output the code….
Thanks for the tip, Chris. I guess I have to learn more about WordPress now that I’ve been using it for quite some time. I’ve incorporated your tip in this blog post. Now, to check the other blog posts that have PowerShell scripts in them 🙂
Hi Edwin, thanks for your post! It helped me getting our version working.. I did change some small stuff to get it to work for our situation. See: http://www.serverside-developer.com/2012/09/automating-sql-log-shipping-over-sftp.html
Great to hear that the solution helped out in a way.
Edwin….Its a script but I am getting an error for out-file, any idea?
——————————————————————————————————-
Unexpected token ‘Out-File’ in expression or statement.
At C:scriptsres3.ps1:13 char:93
+ $restoreSQL = “RESTORE LOG $databaseName FROM DISK=$logBackupPath WITH NORECO
VERY” Out-File <<<< D:$databaseName.sql
——————————————————————————————————-
-VJ
As Chris already mentioned, there is a missing pipeline (|) character before the Out-File cmdlet. I’d fixed the posted script before but it seems that it reappeared.