T-SQL generation with PowerShell

Some easy to use scripting to create repetitive T-SQL like restoring SQL Server transaction log backups:

First, have your repeating text patterin in a variable, and insert placeholders where necessary: {0}, {1}, etc.  In this example we only have one placeholder.  For more information about this placeholders, search for “Powershell .Net string formatting”.

$text = “RESTORE LOG [MyDataBase]
FROM DISK = N'{0}’

Then, get the items you want to have in the text, in this example we get the FileInfo objects for the TRN backup files:

Set-Location G:\backup\MyDatabase
Get-ChildItem *.trn

And finally, combine both by piping the FileInfo objects and using the FullName property of those objects to replace the {0} placeholder in our text by means of the “-f” operator:

Set-Location G:\backup\MyDatabase
Get-ChildItem *.trn | ForEach-Object { $text -f  $_.Fullname } | Out-File “restore.sql”

That’s all there’s to it.  Experiment with Import-CSV instead of Get-ChildItem to get your information from a file, and outputting xml instead of T-SQL. It will prove very helpfull in automating batch commands and text.  For example: I’ve used it to generate an xml config file for a remote desktop manager tool starting from an Excel list of a few dozen of  servernames in a matter of seconds.

Have fun!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s