Powershell script to build up restore commands for SQL Server

I often need to restore a standby server up to the latest production restore, either for testing or because I to get it ready for mirroring. As it’s on remote server there’s no easy list to click on in SQL Server Management Studio. So it’s time to look at the directory listing, and then play around with it in a text editor. With transaction logs every 5 minutes just getting close to completing the editing in time is a pain in the proverbial. So deciding to go the easy route (always my preference), here’s a powershell script that’ll build the statements for you:

$start="restore database $dbname from disk='$folder"
$end="' with norecovery"

$holder = dir $folder *.bak
$tholder = dir $folder *.trn
"$start{0}$end" -f $holder[-1].name
$tholder | where {$_.lastwritetime -gt $holder[-1].lastwritetime} | foreach {"$start$_$end"}

Nothing too fancy there. I tend to keep more than a days worth of backups on disk if possible, so it looks in the specified folder for the oldest .bak file, and then lists all the .trn files created since then.

I normally just call it as:

./restore.ps1 ProdDB \\backupserver\backupshare$\proddb_backups > out.txt

If you wanted to be brave you could pipe the output into osql to avoid a cut ‘n’ paste, but I’m not usually that keen. For anything of an interesting size or backup frequency you’ll probably have to run it a couple of times to get up to date.

I’ve one in the works that talks to a SQL instance to get data file locations and the puts in the moves if needed. But it’s not quite ready for public eyes yet

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply