Home > SQL Azure, SQL Azure Admin > How to Backup Azure Database within SQL Azure

How to Backup Azure Database within SQL Azure


Currently there is no built in Backup option to backup SQL Azure databases from Management Studio

image

You also can’t use BACKUP DATABASE command

image

There is a new option to create a copy of your database quickly from SQL Azure within the same server using

CREATE DATABASE <TargetDB> AS COPY OF <SourceDB>

Ex.

CREATE DATABASE NorthWind_DB_Copy AS COPY OF Northwind_DB;

By running this SQL Command in Master Database, you can create a Copy of NorthWind_DB to Northwind_DB_Copy

Using the following query you can get the status of Copy Status

— Query to fetch the state of the new database

SELECT

           DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

image

 

— Get the Status of Database Copying

SELECT * FROM SYS.DM_DATABASE_COPIES 

        WHERE DATABASE_ID = DB_ID(‘NorthWind_DB_Copy’)

image

 

Once the copying is complete, Database will be Online and available for access

SELECT

DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

image

Advertisements
  1. sachin nikam
    July 25, 2011 at 7:40 am

    CREATE DATABASE [localserver.]new_database_name AS COPY OF
    [[azureserver].database.windows.net.]database_name

    after executing this, gives error-

    Msg 40530, Level 15, State 1, Line 1
    The CREATE DATABASE statement must be the only statement in the batch.

    Like

    • July 25, 2011 at 8:09 am

      Hi Sachin,

      CREATE DATABASE AS COPY OF command is specifically for creating a copy of SQL Azure database within or across SQL Azure servers. You cannot use this command to create a copy of your local database to SQL Azure.

      For migrating you local database to SQL Azure, Please use SQL Azure Migration Wizard http://sqlazuremw.codeplex.com/

      Like

  1. No trackbacks yet.

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

%d bloggers like this: