How to copy and backup SQL Databases in Azure


Let’s say you want a copy of your database for some reasons.  Scenarios might vary.  Maybe you want to bring back production DB to another environment.

There are at least three ways I could think to do that with the Azure Portal:

  • Copy the Database
  • Restore a version of the Database
  • Backup / Restore the current Database

Copy the Database

This option is quite straightforward.  We will copy the current state of the database into a new database.

In the portal, go to your database blade and press the copy button on top.

image

In the Copy blade, type the name of the new database & choose which logical server it should be attached to.  Press OK.

This will create a copy of the database.

The copy will be of the same pricing tier as the original.  This can be changed afterwards using the portal.

The copy will be in the same resource group as the original.  This can be changed afterwards using PowerShell scripts.

Restore a version of the Database

Azure SQL Database are automatically backed up in the background in order to create Point in time Restore.

Depending on your pricing tier, you can go back different length of time.  For instance, standard tier allows you to go back 14 days.

So again, go to your Database blade.  Click the restore button.

image

Type a database name for the target (copy) and choose a point in time.  Click Ok.

The copy will be in the same logical server, the same resource group and have the same pricing tier as the source.

Backup / Restore the current Database

This option can be interesting if you want to keep a copy of the backup for multiple restore (e.g. maybe you are testing a script), you want to carry the database easily across subscription or you want to restore it on-premise.

In your database blade, click Export.

image

The target will be a file in blob storage.  You’ll need to specify the name of the file, the location (storage account and container) and the login information of an admin account on the logical SQL Server.  Click ok.

Once the DB is exported you can use it to restore it somewhere.

In order to do that, open your SQL Server blade and click Import Database.

image

From there you’ll specify where to source the backup file, on which logical SQL Server to create the database, with which collation and the name of the DB.

Conclusion

As you can see, Azure SQL Database lets you copy its content in many ways.

One of the most interesting way is the point in time recovery.  This is interesting because it allows you to easily go back in time.

Since Azure SQL Database is triple redundant, chances are you’ll never have to perform a disaster recovery on it (unless the Azure Region you selected gets flooded or falls victim of an earthquake).  So point in time recovery, or OUPS recovery, is what you’ll use.

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