Category Archives: powershell

Katherine’s Excellent Log-Shipping Adventure

Or, Log-Shipping over 1200 databases automagically.

Picture1

Continue reading

Leave a Comment

Filed under monitoring, powershell, sql

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

Rename a lot of databases with PowerShell

I was given the task of renaming a bunch of databases named something like “name,” “name_1,” “name_2,” etc., to “nameold,” “nameold_1,” “nameold_2,” etc.  Our convention is for the database name to match the file names.  So…

$controlfile=”path\to\listofdatabases.txt”
$server=”servername\instance”
$mdfpath=”c:\path\to\Data”
$ldfpath=”c:\path\to\Log”
$dbnameappend=”old”
$dbnameappendarchive = $dbnameappend + “_”

foreach ($dbname in get-content $controlfile)
{
$newdbname = $dbname + $dbnameappend
if ($dbname -match ‘_[0-9]’) {$newdbname = $dbname -replace ‘_’, $dbnameappendarchive}
Write-Host $newdbname

$mdf=$dbname + “.mdf”
$ldf=$dbname + “_log.ldf”
$newmdf=$newdbname + “.mdf”
$newldf=$newdbname + “_log.ldf”

$query = “ALTER DATABASE ” + $dbname + ” SET SINGLE_USER WITH ROLLBACK IMMEDIATE;”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $dbname + ” MODIFY NAME = ” + $newdbname
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET offline”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]_log’, FILENAME = ‘” + $ldfpath + “\” + $newldf + “‘)”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]’, FILENAME = ‘” + $mdfpath + “\” + $newmdf + “‘)”
sqlcmd -E -S $server -Q $query

Move $ldfpath\$ldf $ldfpath\$newldf -Force
Move $mdfpath\$mdf $mdfpath\$newmdf -Force

$query = “ALTER DATABASE ” + $newdbname + ” SET online”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET MULTI_USER;”
sqlcmd -E -S $server -Q $query

}

This was so fast that I decided to share the joy. 🙂

(I plan to use this script if I ever need to bring my log-shipped databases online, hence the append.  I might need to do thing_oldprod, stuff_oldprod, whatsit_oldprod, etc.)

Leave a Comment

Filed under powershell, scripting

I am a winner!

I went to the Atlanta PowerShell Users Group tonight.  The topic was tips and tricks.  My trick was this script, which task scheduler reads to me in the morning. Assuming you use Hiveminder–and you should, because it’s awesome–you only have to edit the RSS locations for your location and for your to do list.

$weatherurl = "[yahoo weather RSS and city code]"
$hiveminderurl = "[hiveminder rss url here]"

You’ll need your yahoo weather city code–for example, this is Alpharetta, GA–and the RSS feed of your hiveminder to-do list for today.

I won a copy of PowerShell in Depth:  An Administrator’s Guide, by Don Jones, Richard Siddaway, and Jeffery Hicks.  Awesome!

(Download.)

Leave a Comment

Filed under geekiness, powershell, scripting

Fun with Exchange Management Shell

I’m really digging the Exchange Management Shell.  It’s FUN FOR THE WHOLE FAMILY!

Okay, maybe not the whole family.  😉

Short example:  someone wanted to know if I could tell them how many emails someone sent in a specific time period.  Short answer:

Get-MessageTrackingLog -Sender username@yourdomain.com -EventID “send” -start ‘1/1/13’ -end ‘2/20/13’ -resultsize unlimited | select-object sender | Group-Object pattern | select count

I spent time playing with that command and checking out how many emails we’ve sent offsite since the start of the year and such.  Better than a water park, I’m telling you!

Leave a Comment

Filed under powershell, sysadmin