SMO vs Invoke-Sqlcmd in Azure

Home / Azure / 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:

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 = ';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"]


It worked flawlessly. Hmmm… Maybe there’s an extra command being passed in from Invoke-Sqlcmd? Not sure. But it’s interesting.


  • I will have to double check my article on managing a windows Azure database.

    I found some commands like (if db exist, drop db) did not work.

    However, wrapping them in a Exec(”) worked fine. See below.

    The cloud, a new set of issues and opportunities!

    — Delete existing database
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N’AUTOS’)

OK, fine, but what do you think?