SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


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

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...