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. I’m passing in a single command. It’s not part of any other commands or statements or batches. What if I did the same thing in SMO?
$cn = 'Server=tcp:server.database.windows.net;User ID=CloudAdmin;[email protected];Trusted_Connection=False;' $conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = $cn $srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $conn $db = $srv.Databases["master"] $db.ExecuteNonQuery($sql)
It worked flawlessly. Hmmm… Maybe there’s an extra command being passed in from Invoke-Sqlcmd? Not sure. But it’s interesting.