While PowerShell is very, er… powerful, I’m never a fan of using a particular tool just because I can. If I’m doing SQL work, then I generally prefer to do it with SQL scripts. However, as a lot of my build and process work is already built with PowerShell, it’s very easy to call out to my scripts from TeamCity with a simple PS script:
"Restoring Production Backups"
Invoke-Sqlcmd -InputFile "D:\Scripts\Restore Production Backups.sql" -ServerInstance "localhost" | Out-File "D:\Scripts\Output.txt"
I also found the following article a big help after getting errors calling the Invoke-Sqlcmd cmdlet. I would recommend trying the steps in the reverse order though, as all I had to do to my script was add the snap-ins, rather than installing all the tools which were already present.