CODEWITHSUNDEEP
t-sqlazureazure-sql-database
mtmurdock
mtmurdock

Reputation: 13062

TSQL: How do I move data between SQLServer instances?

I have two SQLSever instances, each of them have an identical schema. One is running in SQLAzure, the other is a standared SQLServer 2008 instance. I need to copy the data from the Azure database to my local instance.

Essentially I want to do this:

insert LOCAL_TABLE (col1, col2)
select col1, col2
from AZURE_TABLE

How would I do that?

Upvotes: 2

Views: 1164

Answers (5)

Lynn Langit
Lynn Langit

Reputation: 4060

Is this a one-time copy or ongoing? If one-time, then use the SQL Azure Migration Wizard (from Codeplex) If ongoing then use SQL Azure data sync

Also you can verify that the schema is compliant with SQL Server Data Tools in VS, just set the target to SQL Azure or to SQL Server 2012, or 2008 and then build and any/all schema errors will show up.

Upvotes: 0

astaykov
astaykov

Reputation: 30903

In order to move data between SQL Servers, and if one of them is SQL Azure you have couple of options:

  1. SQL Azure Data Sync
  2. Using SSIS
  3. Write your own code that will move data using, most probably SqlBulkCopy class.
  4. If you would like to just copy all the data, you could also use SQL Azure Migration Wizard - you can omit the option for coping the schema, and let it just copy the data.

EDIT

And as, by the original answer from Matthew PK, you could link to your SQL Azure server from your on-prem Server, but this is only an option when you just want to do some ad-hoc testing. I would not use this approach in production for constantly syncing data.

Upvotes: 3

AGoodDisplayName
AGoodDisplayName

Reputation: 5653

Use a product like "SQL Data Compare" from redgate. I am not a Azure user, but I am guessing it would work, I have used it for a few years and its pretty solid.

Upvotes: 0

paras_doshi
paras_doshi

Reputation: 997

Linked Server is not officially supported. However, Here are couple of resources that are supported and would help you do what you are looking for:

1) Check out SQL Azure Dac Examples: http://sqldacexamples.codeplex.com/

2) The other options is SQL Azure Data SYNC.

Upvotes: 0

Matthew
Matthew

Reputation: 10444

You could accomplish that in a single statements using linked servers.
http://msdn.microsoft.com/en-us/library/ms188279(v=sql.100).aspx

EDIT: Here is a link which appears to explain how to link to SQL Azure:
http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

EDIT: Here is a write-up on connecting to Azure with SSMS
http://www.silverlighthack.com/post/2009/11/11/Connecting-to-SQL-Azure-with-SQL-Server-Management-Studio-2008-R2.aspx

Otherwise I believe you need to do it in two statements.

Upvotes: 1

Related Questions