SMO vs Invoke-Sqlcmd in Azure
I'm working on a series of Powershell scripts for the pre-conference seminars on Windows Azure SQL Database that I'm going to be giving at TechEd and the PASS Summit. One of the things I want to do is script out a full backup process (one that's consistent with transactions) which means I need to create a database copy. There's a nice neat T-SQL command that does this: CREATE DATABASE ADW_COPY AS COPY OF nmhdhskcew.AdventureWorks2012; So, being the lazy Powershell freak that I am, I figured I'd just pass it in with Invoke-sqlcmd as a single line like this: Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $user -Password $password -Query $sql Which resulted in an unexpected error: Invoke-Sqlcmd : The CREATE DATABASE statement must be the only statement in the batch. Well, now I'm curious.…