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. 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;Password=@password;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.

3 thoughts on “SMO vs Invoke-Sqlcmd in Azure

  • 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’)
    EXEC (‘DROP DATABASE [AUTOS]’);
    GO

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.