Thursday, March 21, 2013

Extracting data from SQL Azure using Powershell

I have a SQL Website that is logging some debug output to a SQL Azure DB Linked Resource. In order to do some quick troubleshooting, I tried using the Manage interface in Azure, however it is lacking a way to export the data. I could manually copy and paste out database rows and columns, but that is a pain. I don't have SQL 2013 Management Studio and just want a lightweight way for me to access my databases.

I ended up doing this with Powershell:


$connectionString = "Data Source=$datasource;Initial Catalog=$database;User ID=$userid;Password=$password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
$SqlCmd=New-Object System.Data.SqlClient.SqlCommand($CommandText)
$SqlCmd.Connection=New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlAdapter=New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)
$DataSet=New-Object System.Data.DataSet
I can then get at the data through $DataSet.

I can also get the connection string though the Windows Azure console and go to SQL Databases, select my database, select "View SQL Database connection strings for ADO .Net, ODBC, PHP, and JDBC", copy the ADO.NET connection string, and replace "Server" with "Data Source", "Database" with "Initial Catalog", and "{your_password_here}" with my password.

No comments:

Post a Comment