Azure SQL databases¶

Afi provides comprehensive backup and recovery support for Azure SQL databases, as well as online preview and export for database data and metadata.

Browse¶

Inside the database backup, you can browse its configuration, schema, and table data.

Configuration¶

Table data¶

The database data preview allows you to navigate across data tables and view all cell values for a specific row by clicking on that row:

Restrict table content preview¶

You can restrict access to database table data for service administrators or access group operators on the Service → Settings → Access groups tab by disabling the Preview content access permission. Here is an example of table browse view when content preview is restricted:

Schema¶

Afi saves the database schema as part of the backup, allowing you to browse it as a collection of SQL scripts that describe database tables, constraints, views, procedures, and more.

At the root of the schema browse view, you can find an sql_schema.dacpac file containing the complete description of the database’s data structure in DACPAC format. You can use this file to recreate the database with the same data structure configuration as the original.

To view the contents of a DACPAC file locally, add the .tar extension to the file name and unpack it as an archive:

Scripts under the Schema tab are stored in per-schema folders, with a separate folder for each script type (Tables, Functions, Views, etc.):

Preview SQL script content¶

To preview the SQL script content, click the corresponding row:

View SQL script versions¶

To view all versions of an SQL script stored by the service, click the three-dot icon in the corresponding row and then select Show backup versions. A dialog will appear, listing every script version along with the backup snapshot times where each version exists.

You can select and download any of the listed versions:

Search¶

Table data¶

To easily navigate across large tables, Afi lets you search inside a table by primary key value. Supported operations are =, >, >=, <, <=. You can iterate over search results the same way you iterate over table data in general.

Search is available when:

The table has a primary key.
The first column of the primary key uses one of the supported data types listed below.

For tables with a composite primary key, the search uses the first primary-key column only.

Data types supported for searching¶

Data type kind	Data types
String	`varchar`, `char`, `nvarchar`, `sysname`, `nchar`
Integer	`tinyint`, `smallint`, `int`, `bigint`
Floating, decimal, and money	`real`, `float`, `decimal`, `numeric`, `money`, `smallmoney`
Temporal	`date`, `time`, `datetime`, `datetime2`, `datetimeoffset`, `smalldatetime`
GUID	`uniqueidentifier`

Schema¶

Afi allows you to search across SQL scripts backed up as a part of the database schema by script name. The following example shows a search query to find all SQL scripts named SampleTable:

Export¶

Configuration¶

You can copy or download a JSON with the Azure configuration of a database and its SQL server from the backup browse view.

Table data¶

You can download table data for selected tables as CSV. Please note that fields with binary data types are not included in the export.

Schema¶

You can download SQL scripts or entire folders containing scripts by selecting them in the backup browse view and clicking Download:

Recovery¶

You can restore a database from a backup snapshot, creating a new database in the selected SQL Server.

To restore a database:

Click Recover in the database backup browse view to open the recovery dialog.
Enter a name for the database to be restored.
(Optional) Select the Azure subscription ID, resource group, and location for the new database instance.
Select an SQL Server instance where the new database instance will be created.
Select a secret (login/password pair) that the recovery task will use to connect to the restore destination SQL Server.
Click Recover to initiate the database recovery.

Afi will display the recovery progress on the Activity → Tasks tab and send you an email notification once recovery completes.

Info

When you click on the Subscription, Resource group, Location, or SQL Server field, Afi automatically shows a searchable list of the corresponding entities available on the Azure side.

Create a secret for SQL server access¶

You can create a secret (login/password pair) for SQL Server access directly in the recovery dialog by entering the login and password that Afi will use to connect to the server. The secret is securely stored on the Afi side in encrypted format and can be reused for future recoveries with that server.

Info

If you don't want to retain the server credentials on the Afi side after the recovery, you can delete the corresponding secret on the Service → Settings → Secrets tab.