How to copy and backup SQL Databases in AzureSolution ·
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.
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.
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.
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.
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.
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.