Introduction
SQL Server on-premises Data Warehouse is similar to an Azure SQL Data Warehouse (ASDW). However, if we talk about backups, ASDW is very different from on-premises Data Warehouse databases. If we check the BACKUP statement Online Help, we will notice that this statement is not applicable to ASDW:
How can we backup a Data Warehouse?
This picture shows what a DBA needs to do in ASDW to create backups:
In other words, you do not need to worry about creating backups in ASDW because they are created automatically and you can rest and enjoy life (or spend your time in other DBA activities).
This new article will show you the following:
- How does the backups work in ASDW. We will add some data using the query editor and test the backup.
- How to recover a deleted ASDW database.
Requirements
1. An Azure Subscription.
2. The ASDW Database already created in earlier chapters.
Get Started
We will create a table and add some data in order to test the backups and then we will recover the database and then we will also show how to recover a deleted database.
How Do Backups Work in ASDW
In the Azure Portal, g o to your ASDW Database:
Select the Query Editor to create your own queries:
This option is in previous version by the moment. You may receive the terms to sign before using it:
You will need to Login using the credentials specified when you created the ASDW database:
Specify your password:
In this example, we will create a table named dimSalesOffice and insert some data and run the query:
In order to test, you can do a select to check that the data was inserted successfully:
We can insert more data:
insert into dimsalesoffice values (2,'New York')
Select the ASDW and click restore:
By default, when the database is created, a backup is created automatically. This backup is empty and it will not include the tables and data that we just inserted:
A backup is created every 4 hours. Once that the second backup is created, add a new row to the table. In this example, we are adding Washington as a sales office and verifying the results with a select:
Select the Database and the Restore option:
In this example, we have 3 backups: the initial backup at 1:18 and the other ones at 5:13 and 9:13. The backups are created automatically every 4 hours:
Select a restore point and press OK, the backup will be restored. You will receive a Notification about it:
As you can see, the database is not overwritten, it is created a parallel database. You can remove the old one and rename the new one:
In the recovered database run a query to the dimsalesoffice table and verify that the data is OK:
As you can see, there are only 2 rows and not 3. It means that the backup contains the table with 2 rows, which is the expected behavior.
What happens, when we delete a database?
When you delete a Database, it is possible to recover it. Let's delete one ASDW database to test:
Once deleted, go to the > icon and click SQL Servers:
In Overview, select the deleted Databases option:
Select the deleted database and press OK to restore it:
You will be able to see the database online again:
These backups are stored for 7 days for a basic tier (the cheapest option) and 35 days for Standard and Premium tiers. The dropped databases are stored for 7 days. In many cases, these automatic backups are enough. Because we are interested in the new data updated.
In Azure SQL there is a new option to create permanent backups using the Vault option. This option allows to retain backups for up to 10 years. However, this option is not available in ASDW yet:
When we select the SQL Server, we noticed that the ASDW is not detected for a permanent backup.
Conclusions
As you can see, you can easily recover your database. There are backups taken every 4 hours and you can restore to these points in time. In addition, if you delete the database, you can recover it by going to the deleted databases.
We also noticed that the Long-term backup retention, which is in previous version for Azure SQL is not available for ASDW.
References
For more information, refer to these links: