CODEWITHSUNDEEP
sql-serverazuredeployment
martial
martial

Reputation: 3883

How do I publish SQL Server Database to Azure to update existing database

I publish my SQL Server database on my local machine to Azure via Management Studio.

Say I name the database on Azure "California". The first time I do it its ok.

Then I made changes on my database on local, adding columns, adding data, etc. Then I want to deploy this database to Azure again.

But now Management Studio won't let me do it. It says database "California" already exists on Azure.

I can get away with this problem by deleting existing "California" on Azure first, then deploy again. But this seems not correct...Every time I make database changes on local, I need to delete the one on Azure before I deploy?

Does anyone know how to solve this problem?

Upvotes: 3

Views: 14558

Answers (5)

Vijay
Vijay

Reputation: 588

I have been facing the same issue, but finally found Microsoft data migration assistant which allows you to migrate schema and data from a source database to target database (existing).

Tested migrating schema and data from my on-prem SQL server to Azure SQL Database and it worked out very well (just needed to uncheck migrating Users).

It seems to be actively maintained as well, as the last published version was in Q4 2023.

Upvotes: 0

JimbobTheSailor
JimbobTheSailor

Reputation: 1613

Update Jan 2020

(I'm assuming the question is to update the schema on Azure, not update data)

If you are using Code First for creating your database. Then it is really simple.

Assuming your connection string is pointing to a local database (the one you want to update to Azure)

  1. Make the changes to your entities
  2. Create a new migration for those changed (Add-Migration etc)
  3. Update the migrations to your local database.
  4. Now just change the connection string to point to your Azure database
  5. Update the migrations again, which will now update to the Azure database

Voila! That simple

Upvotes: 2

Isuru Fonseka
Isuru Fonseka

Reputation: 601

If you are using SQL 2012, you can export a data tier application (bacpac) file, then import that when you login to your SQL Azure instance.

Upvotes: 0

Aiden Strydom
Aiden Strydom

Reputation: 1208

I know its been a while since the question was asked, however, there is no accepted answer. Thus to aid anyone coming here with a similar issue the following link describes a number of methods.

I prefer deploying a SqlDb from SQL Management Studio

http://azure.microsoft.com/en-us/documentation/articles/sql-database-deploy/

How to: Deploy to SQL Database In Management Studio, connect to an on-premises SQL Server instance that has a database you want to migrate.

1) Right-click the database --> Tasks and click Deploy Database to SQL Azure.

2) In Deployment Settings, enter a name for the database.

3) Click Connect.

4) In Server name, enter the 10-character server name, followed by .database.windows.net.

5) In Authentication, choose SQL Server Authentication.

6) Enter the administrator login name and password that you provisioned when creating the SQL Database logical server.

7) Click Options.

8) In Connection Properties, in Connect to database, type master.

9) Click Connect. This step concludes the connection specification and takes you back to the wizard.

10) Click Next and click Finish to run the wizard.

Upvotes: 1

Craig
Craig

Reputation: 36836

The best way is to create a database project in Visual Studio to maintain your database schema and then you can do a database compare to create a script to update the database on Azure.

http://weblogs.asp.net/gunnarpeipman/archive/2013/01/28/using-visual-studio-database-projects-in-real-life.aspx

RedGate Schema Compare will also do a similar thing.

Upvotes: 3

Related Questions