Development

How to backup local SQL database and restore to Azure SQL database

Hi friends, recently I was finding a way on taking backup of local database and restore that database to Azure SQL database. I found that there is new option available to generate the scripts for Microsoft SQL Azure database using Generate Scripts option.

Let me walkthrough step by step on how we can take the backup of our local database in form of script and run it on Azure SQL database to create a database there.

There are many other options available to take the backup and restore to Azure SQL database but in this blog I will tell you this method.

First I logged in into my SQL 2014 server and attach AdventureWork2012 database to it. Now we have a database to my local system and we will restore this database to Azure SQL database.

1

Select AdventureWorks2012 database > Right Click on it > Select Tasks > Click on Generate Scripts

After clicking on Generate Scripts a new window gets pop up ‘Generate and Publish Scripts’.

2

Click Next.

In this window we can specify whether we want to generate the script for all database objects or want to select specific database objects.  I have selected option ‘Select specific database objects’ and select Tables, Views and Stored Procedure from it.

3

Click Next.

Now in Select Scripting options we give the filename for our script and click on Advance option for more available option.

4

Now in advance scripting options we will change the value for ‘Script for the database engine type’. From the dropdown list select value ‘Windows Azure SQL database’.  You can select other options of your choice and then Click on ‘OK’ button.

5.JPG

In Summary windows we can review out all the selected objects and click ‘Next’.  In ‘Save or Publish Scripts’ windows when all objects get successfully completed indicated with Green marks click on ‘Finish’.

6

After clicking on Finish you can browse the location where you have saved your script.

Now login to your Azure SQL database and open the newly generated script and run it. Your new database on Azure SQL is ready :).

So friends this is one of way to take backup from local database and create that database on Azure SQL database.  In my incoming blogs I will cover others methods to restore the database on Azure SQL.

Have a happy learning 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s